MySQL Insert Into Select

The MySQL INSERT INTO ... SELECT statement is used to insert data into a table by selecting data from one or more source tables. This statement allows you to copy data from one table or multiple tables into another table, or even from a subquery result. It’s a powerful tool for data migration, consolidation, and data manipulation.

The basic syntax for the INSERT INTO ... SELECT statement is as follows:

INSERT INTO target_table (column1, column2, ...)
SELECT source_column1, source_column2, ...
FROM source_table
WHERE condition;

Here’s a breakdown of each part of the statement:

• target_table: This is the name of the table into which you want to insert data.
• (column1, column2, ...): You specify the columns in the target_table into which you want to insert data.
• SELECT source_column1, source_column2, ...: You specify the columns or expressions to select from the source table(s) or subquery.
• FROM source_table: This is the name of the source table from which you want to select data. You can also use a subquery here.
• WHERE condition: This is an optional part of the statement that allows you to filter the data from the source table or apply specific conditions.

Here are some examples of how to use the INSERT INTO ... SELECT statement in MySQL:

1. Copy all records from the old_employees table into the new_employees table:

   INSERT INTO new_employees (employee_id, first_name, last_name, salary)
   SELECT employee_id, first_name, last_name, salary
   FROM old_employees;

2. Insert records into a table from a subquery that combines data from multiple tables:

   INSERT INTO sales (product_id, sales_date, quantity, revenue)
   SELECT p.product_id, s.sales_date, s.quantity, s.quantity * p.price
   FROM products p
   JOIN sales_data s ON p.product_id = s.product_id;

3. Copy records from one table into another, applying a filter condition:

   INSERT INTO high_salary_employees (employee_id, first_name, last_name, salary)
   SELECT employee_id, first_name, last_name, salary
   FROM all_employees
   WHERE salary > 60000;

The INSERT INTO ... SELECT statement is a flexible way to copy and manipulate data from one table or multiple sources into another table. It allows you to perform data transformations and selections as part of the insert operation, making it a versatile tool for data management in MySQL.