SQL INSERT INTO SELECT Statement : SQL INSERT INTO SELECT Statement copies data from one table and inserts it into another table.
- INSERT INTO SELECT requires that data types in source and target tables match
- The existing records in the target table are unaffected
INSERT INTO SELECT Syntax
Copy all columns from one table to another table:
INSERT INTO table2
SELECT * FROM table1
WHERE condition;
Copy only some columns from one table into another table:
INSERT INTO table2 (column1, column2, column3, …)
SELECT column1, column2, column3, …
FROM table1
WHERE condition;
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
The following SQL statement copies “Suppliers” into “Customers” (the columns that are not filled with data, will contain NULL):
Example
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;
Output
You have made changes to the database. Rows affected: 29
The following SQL statement copies “Suppliers” into “Customers” (fill all columns):
Example
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
SELECT SupplierName, ContactName, Address, City, PostalCode,Country FROM Suppliers;
Output
You have made changes to the database. Rows affected: 29