SQL INSERT INTO SELECT Statement

Using SQL, you can copy information from one table to another.

The INSERT INTO SELECT statement copies data from one table and then inserts it into an existing table.

SQL INSERT INTO SELECT Statement

The INSERT INTO SELECT statement copies data from one table and then inserts it into an existing table. Existing rows in the target table are not affected.

SQL INSERT INTO SELECT Syntax

We can copy all columns from one table into another existing table:

INSERT INTO table2
SELECT * FROM table1;

Or we can copy only the desired columns into another existing table:

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

Demo Database

In this tutorial, we will use the tutorialpro sample database.

Below is the data from the "Websites" table:

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | Taobao       | https://www.taobao.com/   | 13    | CN      |
| 3  | tutorialpro.org      | http://www.tutorialpro.org/    | 4689  | CN      |
| 4  | Weibo        | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | Stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

Below is the data from the "apps" table:

mysql> SELECT * FROM apps;
+----+------------+-------------------------+---------+
| id | app_name   | url                     | country |
+----+------------+-------------------------+---------+
|  1 | QQ APP     | http://im.qq.com/       | CN      |
|  2 | Weibo APP  | http://weibo.com/       | CN      |
|  3 | Taobao APP | https://www.taobao.com/ | CN      |
+----+------------+-------------------------+---------+
3 rows in set (0.00 sec)

SQL INSERT INTO SELECT Example

Copy data from the "apps" table into the "Websites" table:

Example

INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps;

Copy only the data with id=1 from the "apps" table into the "Websites" table:

Example

INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps 
WHERE id=1;