Curriculum
Course: MYSQL
Login

Curriculum

MYSQL

MySQL References

0/140
Text lesson

MySQL INSERT SELECT

The MySQL INSERT INTO SELECT Statement

The INSERT INTO SELECT statement copies data from one table and inserts it into another, ensuring that the data types in both the source and target tables match.

Note: Existing records in the target table remain unaffected.

INSERT INTO SELECT Syntax

Copy all columns from one table to another:

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

Copy specific columns from one table into another:

INSERT INTO table2 (column1column2column3, …)
SELECT column1column2column3, …
FROM table1
WHERE condition;

Demo Database

In this tutorial, we will use the well-known Northwind sample database.

Here 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 here is a selection from the “Suppliers” table:

SupplierID

SupplierName

ContactName

Address

City

PostalCode

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

MySQL INSERT INTO SELECT Examples

The following SQL statement copies data from the “Suppliers” table into the “Customers” table. Columns that are not filled with data will contain NULL values:

Example

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

The following SQL statement copies data from the “Suppliers” table into the “Customers” table, filling all columns:

Example

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 the “Customers” table:

Example

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