SQL INSERT INTO SELECT Statement

The INSERT INTO SELECT statement in SQL is a powerful way to copy data from one table to another. It's a more efficient method than inserting rows one by one, particularly when dealing with large datasets.

INSERT INTO SELECT: Definition and Usage

This statement copies data from a source table's result set (defined by a SELECT statement) into a destination table. Crucially, the data types of the source and destination columns must be compatible. Existing data in the target table is not affected; the new rows are added.

Syntax


-- Copy all columns:
INSERT INTO target_table
SELECT * FROM source_table WHERE condition;

-- Copy specific columns:
INSERT INTO target_table (column1, column2, ...)
SELECT column1, column2, ... FROM source_table WHERE condition;

Example Databases

The following examples use simplified versions of the 'Customers' and 'Suppliers' tables:

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

Suppliers Table

SupplierID	SupplierName	ContactName	Address	City	Postal Code	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

Examples

Copying Some Columns

This copies 'SupplierName', 'City', and 'Country' from 'Suppliers' to 'Customers'. Columns not explicitly mentioned in the target table will have NULL values.

Syntax


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

Output


(Rows are added to the Customers table.  New CustomerIDs will be generated.  ContactName and Address will likely be NULL.)

Copying All Columns

Copying all columns from 'Suppliers' to 'Customers'.

Syntax


INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers;

Output


(All columns from Suppliers are copied to Customers.  New CustomerIDs will be generated.)

Copying with a WHERE Clause

Copying only German suppliers.

Syntax


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

Output


(Only German suppliers are copied to the Customers table. New CustomerIDs will be generated.  Other columns will likely be NULL.)