SQL INSERT INTO SELECT
With SQL, you can copy information from one table to another.
The INSERT INTO SELECT statement copies data from a table and then inserts the data into an existing table.
SQL INSERT INTO SELECT statement
The INSERT INTO SELECT statement copies data from a table and then 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;
SELECT * FROM table1;
Or we can just copy the desired columns and insert them into another existing table:
INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;
(column_name(s))
SELECT column_name(s)
FROM table1;
Demo database
In this tutorial, we will use the php sample database.
The following is the data selected from the "Websites" table:
+----+--------------+--- ------------------------+------+---------+
| id | name | url --------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | Taobao | https://www.taobao.com/ | 13 | CN |
| 3 | php Chinese website | http://www.php.cn/ | 4689 | CN |
| 4 | Weibo | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+----------- ----------------+------+---------+
| id | name | url --------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | Taobao | https://www.taobao.com/ | 13 | CN |
| 3 | php Chinese website | http://www.php.cn/ | 4689 | CN |
| 4 | Weibo | 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 of "apps" APP:
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)
+----+------------+ --------------------------+---------+
| 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 the data in "apps" and insert it into "Websites":
Example
INSERT INTO Websites (name, country )
SELECT app_name, country FROM apps;
SELECT app_name, country FROM apps;
Only copy QQ APP to "Websites":
Example
INSERT INTO Websites (name, country)
SELECT app_name, country FROM p
WHERE id=1;
SELECT app_name, country FROM p
WHERE id=1;
