SQL INSERT INTO SELECTStatements

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

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

SQL INSERT INTO SELECT Statement

The INSERT INTO SELECT statement copies data from one table and inserts the data into an existing table. Any existing rows in the target table will not be affected.

SQL INSERT INTO SELECT Syntax

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

INSERT INTO table2
SELECT * FROM table1;

Or we can copy only the specified columns and insert them 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 EXAMPLE sample database.

The following is the data selected from the "Websites" table:

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | Example      | http://www.example.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
| 7  | stackoverflow | http://stackoverflow.com/ |   0 | IND     |
+----+---------------+---------------------------+-------+---------+

The following is the data from the "apps" APP:

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

SQL INSERT INTO SELECT Examples

Copy the data from "apps" and insert it into "Websites":

Example

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

Only copy the data with id=1 into "Websites":

Example

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