HTML tutorial
CSS3 tutorial
Bootstrap tutorial
JavaScript tutorial
JQuery tutorial
AngularJS tutorial
React tutorial
NodeJS tutorial
PHP tutorial
Python tutorial
Python3 tutorial
Django tutorial
Linux tutorial
Docker tutorial
Ruby tutorial
Java tutorial
C tutorial
C ++ tutorial
Perl tutorial
JSP tutorial
Lua tutorial
Scala tutorial
Go tutorial
ASP.NET tutorial
C # tutorial
The INSERT INTO SELECT statement copies data from one table and inserts it into another table
The INSERT INTO SELECT
statement copies data from one table and
inserts it into another table.
The INSERT INTO SELECT
statement requires that
the data types in source and target tables match.
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;
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 |
The following SQL statement copies "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;
The following SQL statement copies "Suppliers" into "Customers" (fill 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 "Customers":
INSERT INTO Customers (CustomerName,
City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany';