SQL INSERT INTO SELECT statement

SQL allows you to copy information from one table to another.

The INSERT INTO SELECT statement copies the data from the table and inserts the data into the existing table. Any existing rows in the target table are not affected.

SQL INSERT INTO SELECT syntax

We can copy all the columns from one table to another that already exists: 

INSERT INTO table2              
SELECT * FROM table1;

Or we can copy the desired column to another existing table: 

INSERT INTO table2               
(column_name(s))              
SELECT column_name(s)             
FROM table1;

Demonstrate the database

In this tutorial, we'll use the famous Northwind sample database.

Here's the data 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

Data selected from the Suppliers table:

SupplierID SupplierName ContactName Address City Postal Code Country Phone
1 Exotic Liquid Charlotte Cooper 49 Gilbert St. Londona EC1 4SD UK (171) 555-2222
2 New Orleans Cajun Delights Shelley Burke P.O. Box 78934 New Orleans 70117 USA (100) 555-4822
3 Grandma Kelly's Homestead Regina Murphy 707 Oxford Rd. Ann Arbor 48104 USA (313) 555-5735

SQL INSERT INTO SELECT instance

Copy the "Suppliers" column to the "Customers" column:

INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers;

Insert only a copy of the German vendor into "Customers":

INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers
WHERE Country='Germany';