The 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 NULL.
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.
Recommended Readings