The INSERT INTO SELECT
statement copies data from one table and inserts it into another, ensuring that the data types in both the source and target tables match.
Note: Existing records in the target table remain unaffected.
Copy all columns from one table to another:
INSERT INTO table2 SELECT * FROM table1 WHERE condition; |
Copy specific columns from one table into another:
INSERT INTO table2 (column1, column2, column3, …) SELECT column1, column2, column3, … FROM table1 WHERE condition; |
In this tutorial, we will use the well-known Northwind sample database.
Here is a selection from the “Customers” table:
CustomerID |
CustomerName |
ContactName |
Address |
City |
PostalCode |
Country |
1 |
Alfreds Futterkiste |
Maria Anders |
Obere Str. 57 |
Berlin |
12209 |
Germany |
2 |
Ana Trujillo Emparedados y helados |
Ana Trujillo |
Avda. de la Constitución 2222 |
México D.F. |
05021 |
Mexico |
3 |
Antonio Moreno Taquería |
Antonio Moreno |
Mataderos 2312 |
México D.F. |
05023 |
Mexico |
And here is a selection from the “Suppliers” table:
SupplierID |
SupplierName |
ContactName |
Address |
City |
PostalCode |
Country |
1 |
Exotic Liquid |
Charlotte Cooper |
49 Gilbert St. |
London |
EC1 4SD |
UK |
2 |
New Orleans Cajun Delights |
Shelley Burke |
P.O. Box 78934 |
New Orleans |
70117 |
USA |
3 |
Grandma Kelly’s Homestead |
Regina Murphy |
707 Oxford Rd. |
Ann Arbor |
48104 |
USA |
The following SQL statement copies data from the “Suppliers” table into the “Customers” table. Columns that are not filled with data will contain NULL values:
INSERT INTO Customers (CustomerName, City, Country) SELECT SupplierName, City, Country FROM Suppliers; |
The following SQL statement copies data from the “Suppliers” table into the “Customers” table, filling all columns:
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers; |
The following SQL statement copies only the German suppliers into the “Customers” table:
INSERT INTO Customers (CustomerName, City, Country) SELECT SupplierName, City, Country FROM Suppliers WHERE Country=‘Germany’; |