The INSERT INTO SELECT statement in MySQL allows you to insert data from one table into another. This is especially useful for copying data or inserting results of a query into an existing table.

Syntax

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

• table2: The target table where data will be inserted.
• table1: The source table from which data is selected.
• The columns in both tables must match in number and data type.

Examples

Example 1: Copy Data from One Table to Another

Goal: Copy all data from the Customers table to a CustomersBackup table.

INSERT INTO CustomersBackup (CustomerID, CompanyName, ContactName)
SELECT CustomerID, CompanyName, ContactName
FROM Customers;

Explanation:

• Data from Customers is selected and inserted into CustomersBackup.
• The column names in both tables must match in number and data type.

Example 2: Copy Data with a Condition

Goal: Copy data for customers from the USA into the USCustomers table.

INSERT INTO USCustomers (CustomerID, CompanyName, ContactName)
SELECT CustomerID, CompanyName, ContactName
FROM Customers
WHERE Country = 'USA';

Explanation:

• Only rows where Country = 'USA' are selected and inserted into USCustomers.

Example 3: Insert Data from Multiple Tables

Goal: Insert combined data from Orders and Customers into a CustomerOrders table.

INSERT INTO CustomerOrders (OrderID, CustomerName, OrderDate)
SELECT o.OrderID, c.ContactName, o.OrderDate
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID;

Explanation:

• The query joins Orders and Customers to retrieve OrderID, ContactName, and OrderDate.
• These values are inserted into the CustomerOrders table.

Example 4: Insert Data into a New Table

Goal: Create a new table and insert data from an existing table.

CREATE TABLE CustomersArchive AS
SELECT *
FROM Customers
WHERE Country = 'Germany';

Explanation:

• The CREATE TABLE ... AS statement creates a new table and inserts the selected rows into it.
• All columns and data types from the Customers table are replicated in CustomersArchive.

Key Points

1. Column Matching:
   • The number of columns in the INSERT INTO statement must match the SELECT statement.
   • Data types in corresponding columns must be compatible.
2. Partial Insert:
   • You don’t need to insert all columns. Just specify the required columns in both INSERT INTO and SELECT.
3. Performance:
   • For large datasets, ensure the target table has proper indexes to optimize insertion.
4. Error Handling:
   • If the target table has unique constraints, duplicate data can cause errors.
   • Use INSERT IGNORE or ON DUPLICATE KEY UPDATE to handle such scenarios.

Advanced Example: Avoid Duplicates

Goal: Insert orders into a backup table, avoiding duplicates.

INSERT INTO OrdersBackup (OrderID, CustomerID, OrderDate)
SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE OrderID NOT IN (SELECT OrderID FROM OrdersBackup);

Explanation:

• The subquery checks if the OrderID already exists in OrdersBackup.
• Only non-duplicate rows are inserted.

When to Use INSERT INTO SELECT

• Data Migration: Move or copy data between tables.
• Data Transformation: Perform calculations or transformations while copying data.
• Data Archival: Backup data into separate tables for long-term storage.