Curriculum
Course: SQL
Login

Curriculum

SQL

SQL References

0/80

MySQL Functions

0/139

SQL Server Functions

0/84

SQL Quick Ref

0/1
Text lesson

SQL INSERT INTO SELECT

The SQL INSERT INTO SELECT Statement

The INSERT INTO SELECT statement duplicates data from one table and inserts it into another.

It necessitates matching data types between the source and target tables.

Note: that the existing records in the target table remain unaffected.

INSERT INTO SELECT Syntax

Duplicate all columns from one table to another.

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’ll utilize the widely known Northwind sample database.

Here is an excerpt 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 here’s a portion of 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

Duplicate the “Suppliers” table into “Customers” (columns without data will be populated with NULL).

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

Example

Replicate the “Suppliers” table into “Customers” (populate all columns).

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

Example

Replicate solely the German suppliers into the “Customers” table.

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