SQL INSERT INTO SELECT statement
With SQL, you can copy information from one table to another.
INSERT INTO SELECT statement to copy data from a table and then insert data into an existing table.
SQL INSERT INTO SELECT statement
INSERT INTO SELECT statement to copy data from a table and then insert data into an existing table. Target table any rows that already exist are not affected.
SQL INSERT INTO SELECT syntax
We can replicate all columns from a table into another existing table:
INSERT INTO table2
SELECT * FROM table1;
SELECT * FROM table1;
Or we can just copy the desired columns 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;
The demo database
In this tutorial, we will use w3big sample database.
The following is a selected "Websites" table data:
+----+--------------+---------------------------+-------+---------+ | id | name | url | alexa | country | +----+--------------+---------------------------+-------+---------+ | 1 | Google | https://www.google.cm/ | 1 | USA | | 2 | 淘宝 | https://www.taobao.com/ | 13 | CN | | 3 | 本教程 | http://www.w3big.com/ | 4689 | CN | | 4 | 微博 | http://weibo.com/ | 20 | CN | | 5 | Facebook | https://www.facebook.com/ | 3 | USA | | 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND | +----+---------------+---------------------------+-------+---------+
Here is the "apps" APP data:
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 data "apps" are inserted into the "Websites" in:
Examples
INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps;
SELECT app_name, country FROM apps;
QQ only complex of APP to "Websites" in:
Examples
INSERT INTO Websites (name, country)
SELECT app_name, country FROM p
WHERE id=1;
SELECT app_name, country FROM p
WHERE id=1;