SQLINSERT INTO SELECT Statement
The SQL INSERT INTO SELECT Statement
TheINSERT INTO SELECT statement copies data from one table and inserts it into another table.
TheINSERT INTO SELECT statement requires that the data types in source and target tables match.
Note: The existing records in the target table are unaffected.
INSERT INTO SELECT Syntax
Copy all columns from one table to another table:
INSERT INTOtable2
SELECT * FROMtable1
WHEREcondition;
SELECT * FROMtable1
WHEREcondition;
Copy only some columns from one table into another table:
INSERT INTOtable2(column1,column2,column3, ...)
SELECTcolumn1,column2,column3, ...
FROMtable1
WHEREcondition;
SELECTcolumn1,column2,column3, ...
FROMtable1
WHEREcondition;
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below 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 a selection from the "Suppliers" table:
| SupplierID | SupplierName | ContactName | Address | City | Postal Code | Country |
|---|---|---|---|---|---|---|
| 1 | Exotic Liquid | Charlotte Cooper | 49 Gilbert St. | Londona | 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 |
SQL INSERT INTO SELECT Examples
Example
Copy "Suppliers" into "Customers" (the columns that are not filled with data, will contain NULL):
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;
SELECT SupplierName, City, Country FROM Suppliers;
Example
Copy "Suppliers" into "Customers" (fill all columns):
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers;
SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers;
Example
Copy only the German suppliers into "Customers":
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany';
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany';
