INSERT INTO SELECT statement is used to copy records from one table to another existing table.
-- 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;
destination_tableis 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_tableis 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
- the column names of the
OldCustomerstable and the
Customerstable 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
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.