SQL INSERT INTO SELECT Statement

The SQL INSERT INTO SELECT statement is used to copy records from one table to another existing table.

Example

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

Here, the SQL command copies all records from the Customers table to the OldCustomers table.


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.

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 you want to copy data to a new table (rather than copying to an existing table), you 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,

-- 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.


Also Read:

Did you find this article helpful?

Our premium learning platform, created with over a decade of experience and thousands of feedbacks.

Learn and improve your coding skills like never before.

Try Programiz PRO
  • Interactive Courses
  • Certificates
  • AI Help
  • 2000+ Challenges