MySQL : INSERT INTO SELECT Statement

In MySQL, the INSERT INTO SELECT statement allows you to insert data into a table from the result set of a SELECT query. This is particularly useful when you want to copy data from one table to another or insert data based on the result of a query.

Here's the basic syntax:


INSERT INTO table1 (column1, column2, ...)
SELECT column1, column2, ...
FROM table2
WHERE condition;
 

  • table1 is the table where you want to insert the data.
  • column1, column2, ... are the columns in table1 where you want to insert the data.
  • table2 is the table from which you're selecting the data.
  • The SELECT statement specifies the columns and possibly filters the rows you want to insert into table1.

For example, suppose you have a table named employees with columns employee_id, first_name, last_name, and salary, and you want to insert data into another table named employees_archive where the salary is above $50,000:


INSERT INTO employees_archive (employee_id, first_name, last_name, salary)
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > 50000;
 

This query inserts rows into the employees_archive table, copying the employee_id, first_name, last_name, and salary columns from the employees table, but only for employees with a salary above $50,000.

It's important to ensure that the columns selected in the SELECT statement match the columns being inserted into in the INSERT INTO statement, both in number and in data type.