SQL INSERT INTO SELECT Statement

-- copy data to an existing table
INSERT INTO OldCustomers
SELECT *
FROM Customers;

INSERT INTO SELECT Syntax

The syntax of the SQL INSERT INTO SELECT statement is:

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

Here,

• destination_table is the name of the table where the data is to be inserted
• column1, column2, column3, ... are the names of the columns to be copied
• source_table is the name of the table from where you want to select the data.

Copy Selected Columns Only

We can also copy only the selected columns from one table to another. For example,

-- copy selected columns only
INSERT INTO OldCustomers(customer_id, age)
SELECT customer_id, age
FROM Customers;

Here, the SQL command only copies records from the customer_id and country columns 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,

-- copy rows that satisfy the condition
INSERT INTO OldCustomers
SELECT *
FROM Customers
WHERE country = 'USA';

Here, the SQL command only copies those rows whose country column value is USA.

Copy Data From Two Tables to One

We can also copy records from two different tables using the JOIN clause with INSERT INTO SELECT. For example,

-- copy contents after joining two tables
INSERT INTO CustomersOrders
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 amount from the Orders table to an existing table CustomersOrders.

To learn more, visit SQL JOIN.

Note: If a table already has data in it, the INSERT INTO SELECT statement appends new rows to the table.

Recommended Readings

• SQL INSERT INTO Statement