TL;DR – The SQL INSERT INTO SELECT statement can copy some or all columns from one table into another. The existing table rows are not affected.
Contents
- 1. Copying all columns to another table
- 2. Copying a few columns to another table
- 3. Example using demo databases
- 3.1. The Developers table
- 3.2. The Customers table
- 3.3. Copying a few columns from Customers to Developers
- 3.4. Copying the Customers table into the Developers table
- 3.5. Copying only UK Customers into the Developers table
Copying all columns to another table
Example
INSERT INTO mytable2
SELECT * FROM mytable1
WHERE condition;Copying a few columns to another table
Example
INSERT INTO mytable2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM mytable1
WHERE condition;Example using demo databases
The Developers table
| ID | Name | City | Country |
|---|---|---|---|
| 1 | Tom Kurkutis | New York | USA |
| 2 | Ana Fernandez | London | UK |
| 3 | Antonio Indigo | Paris | France |
| 4 | Aarav Kaelin | Delhi | India |
| 5 | Andrew Tumota | Miami | USA |
The Customers table
| ID | Name | Contact | Address | City | Postal_Code | Country |
|---|---|---|---|---|---|---|
| 1 | Ben Choplinks | Ben Choplink | Obeesre Str. 51 | Rome | 11207 | Italy |
| 2 | Donald Rich | Donald Richario | Avda. de la Confgfstitución 4122 | Tallin | 17021 | Estonia |
| 3 | Lilly Smilkins | Lilly Smilkin | Matadsderos 2312 | Eguero | 14023 | Mexico |
| 4 | Brandinina | Tom Hitchins | 110 Hanegover Sq. | London | WB2 2DP | UK |
| 5 | Carizmos | Christiano Kerrys | Berguvsesvägen 9 | Luleå | S-968 43 | Sweden |
Copying a few columns from Customers to Developers
Example
INSERT INTO Developers (Name, Country)
SELECT Name, Country FROM Customers;Copying the Customers table into the Developers table
Example
INSERT INTO Developers (Name, City, Country)
SELECT Name, City, Country FROM Customers;Copying only UK Customers into the Developers table
Example
INSERT INTO Developers (Name, City, Country)
SELECT Name, City, Country FROM Customers
WHERE Country='UK';