MySQL INSERT INTO SELECT

MySQL INSERT INTO SELECT Statement

The INSERT INTO SELECT statement in MySQL is used to copy data from one table and insert it into another table. This is particularly useful for transferring or archiving data between tables or databases.

Syntax

INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ...
FROM source_table
[WHERE condition];

Key Components:

1. target_table: The table where data will be inserted.
2. source_table: The table from which data will be selected.
3. column1, column2, ...: The columns in the target and source tables must match in number and data types.
4. WHERE condition: (Optional) Specifies which rows to copy from the source table.

Examples

1. Copy All Data Between Tables

Copy all rows and columns from one table to another.

INSERT INTO employees_backup
SELECT * FROM employees;

Explanation:

• Copies of all rows and columns from employees to employees_backup.

2. Copy Specific Columns

Insert data into specific columns in the target table.

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

Explanation:

• Copies only the employee_id, name, and department columns from employees to employees_backup.

3. Copy Data with a Condition

Insert only selected rows based on a condition.

INSERT INTO high_salary_employees (employee_id, name, salary)
SELECT employee_id, name, salary
FROM employees
WHERE salary > 50000;

Explanation:

• Insert rows of employees earning more than 50,000 into high_salary_employees.

4. Copy Data Between Tables in Different Databases

Insert data into a table in another database.

INSERT INTO archive_db.employees_backup
SELECT * FROM current_db.employees
WHERE department = 'Sales';

Explanation:

• Copies rows of employees in the "Sales" department from the current_db database to the archive_db database.

5. Insert Data with Calculations

Insert data with derived values or transformations.

INSERT INTO sales_summary (employee_id, total_sales, bonus)
SELECT employee_id, SUM(sales_amount), SUM(sales_amount) * 0.1 AS bonus
FROM sales
GROUP BY employee_id;

Explanation:

• Calculates the total sales and bonus (10% of sales) for each employee and inserts the results into sales_summary.

Common Use Cases

1. Archiving Data:
   • Move historical data to a backup or archive table.
2. Data Transformation:
   • Perform calculations or transformations while copying data.
3. Merging Tables:
   • Combine data from multiple source tables into one table.
4. Creating Subsets:
   • Create a table with filtered or summarized data for analysis.

Important Points

1. Column Matching:

   • Ensure the columns in the INSERT INTO clause matches the columns in the SELECT clause in terms of order and data type.

2. Target Table Existence:

   • The target table must exist before running the query. Use CREATE TABLE if needed.

3. Avoid Duplicates:

   • Use the DISTINCT keyword in the SELECT query to prevent inserting duplicate rows:

     INSERT INTO employees_backup
     SELECT DISTINCT * FROM employees;
     

4. Error Handling:

   • If the target table has unique constraints or foreign key constraints, ensure the inserted data complies with these rules.

5. Default and Auto-Increment Values:

   • If the target table has columns with default values or AUTO_INCREMENT, you can exclude those columns in the INSERT INTO clause.

Performance Tips

1. Batch Inserts:

   • For large data transfers, break the query into smaller batches to reduce lock contention.

2. Index Considerations:

   • Disable or drop indexes on the target table during the insertion to speed up the process, and rebuild them afterward.

3. Use Temporary Tables:

   • If transformations are complex, store intermediate results in a temporary table before final insertion.

Example: Copy Data Between Two Related Tables

Imagine two tables, orders and order_archive. To move completed orders to an archive table:

INSERT INTO order_archive (order_id, customer_id, order_date, status)
SELECT order_id, customer_id, order_date, status
FROM orders
WHERE status = 'Completed';

-- Optionally delete the moved records from the source table
DELETE FROM orders
WHERE status = 'Completed';

Let me know if you need further assistance or specific examples tailored to your use case!