INSERT INTO SELECT statement is used to copy records from one table to another existing table. For example,
INSERT INTO OldCustomers SELECT * FROM Customers;
Here, the SQL command copies all records from the Customers table to the OldCustomers table.
Note: To run this command,
- the database must already have a table named OldCustomers
- the column names of the OldCustomers table and the Customers table must match
If we want to copy data to a new table (rather than copying in an existing table), we should use the SELECT INTO statement.
Copy Selected Columns Only
We can also copy only the selected columns from one table to another. For example,
INSERT INTO OldCustomers(customer_id, age) SELECT customer_id, age FROM Customers;
Here, the SQL command only copies records from the customer_id column and country column to the OldCustomers table.
Note: If there are columns other than customer_id and age in the OldCustomers table, the value of those columns will be
Copy Records Matching a Condition
We can use the
WHERE clause with
INSERT INTO to copy those rows that match the specified condition. For example,
INSERT INTO OldCustomers SELECT * FROM Customers WHERE country = 'USA';
Here, the SQL command copies rows that are the value of the country column as USA.
Copy From two Tables to One
We can also copy records from two different tables using the
JOIN clause with
INSERT INTO SELECT. For example,
INSERT INTO OldCustomers SELECT Customers.customer_id, Customers.first_name, Orders.amount FROM Customers JOIN Orders ON Customers.customer_id = Orders.customer_id;
Here, the SQL command copies customer_id and first_name from the Customers table and the amount from the Orders table in an existing table OldCustomerOrders. To learn more, visit SQL JOIN.
Note: If records are already present in an existing table, new rows will be appended. Columns in the existing table may throw errors such as
NOT NULL Constraint Failed,
UNIQUE Constraint Failed while copying data.
Avoid Duplicates in INSERT INTO SELECT
If there is already a row with a similar value, SQL may throw an error while using the
INSERT INTO SELECT command.
However, we can skip copying duplicate rows using the
NOT EXISTS clause. For example,
INSERT INTO OldCustomers(customer_id, age) SELECT customer_id, age FROM Customers WHERE NOT EXISTS( SELECT customer_id FROM OldCustomers WHERE OldCustomers.customer_id = Customers.customer_id );
Here, the SQL command will only copy row to a new table if the customer_id does not have the same value.