INSERT INTO ... SELECT in MySQL

The INSERT INTO ... SELECT statement is used to insert data from one table into another based on a SELECT query.

1. Syntax

INSERT INTO destination_table (column1, column2, column3, ...)SELECT column1, column2, column3, ...FROM source_tableWHERE condition;

✅ Copies selected columns from source_table to destination_table.

2. Example: Copy Data Between Tables

employees Table (Source)

employees_backup Table (Destination)

Query: Copy Data

INSERT INTO employees_backup (id, name, department, salary)SELECT id, name, department, salary FROM employees;

✅ Now employees_backup contains the same records as employees.

3. Insert with Filtering (WHERE Clause)

You can filter data before inserting.

INSERT INTO employees_backup (id, name, department, salary)SELECT id, name, department, salary FROM employeesWHERE department = 'IT';

✅ Only IT department employees are copied.

4. Insert with Column Modifications

You can modify values while inserting.

INSERT INTO employees_backup (id, name, department, salary)SELECT id, name, 'Former Employee', salary * 0.9 FROM employees;

✅ Changes department to "Former Employee" and reduces salary by 10%.

5. Insert from Multiple Tables (JOIN)

You can use JOIN to insert data from multiple tables.

INSERT INTO employees_backup (id, name, department, salary)SELECT e.id, e.name, d.department_name, e.salaryFROM employees eINNER JOIN departments d ON e.department_id = d.id;

✅ Joins employees and departments before inserting data.

6. Insert Without Specifying Columns

If both tables have the same structure, you can skip column names.

INSERT INTO employees_backupSELECT * FROM employees;

⚠️ Not recommended – Can break if table structure changes.

7. Performance Tips

⚡ Use Indexing – Ensure destination_table has indexed columns for better performance.
⚡ Use LIMIT – If the source table is large, insert data in batches using LIMIT 1000.
⚡ Disable Constraints – Temporarily disable FOREIGN KEY constraints to speed up bulk inserts.

8. Key Takeaways

✅ INSERT INTO ... SELECT copies data from one table to another.
✅ Can be combined with WHERE, JOIN, and modifications.
✅ Efficient for data migration and backups.
✅ Ensure column types match to avoid errors.