SQL INSERT INTO SELECT 語句
SQL INSERT INTO SELECT 語句
通過 SQL,您可以從一個表復製信息到另一個表。
INSERT INTO SELECT 語句從一個表復製數據,然後把數據插入到一個已存在的表中。
SQL INSERT INTO SELECT 語句
INSERT INTO SELECT 語句從一個表復製數據,然後把數據插入到一個已存在的表中。目標表中任何已存在的行都不會受影響。
SQL INSERT INTO SELECT 語法
我們可以從一個表中復製所有的列插入到另一個已存在的表中:
INSERT INTO table2
SELECT * FROM table1;
SELECT * FROM table1;
或者我們可以只復製希望的列插入到另一個已存在的表中:
INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;
(column_name(s))
SELECT column_name(s)
FROM table1;
演示數據庫
在本教程中,我們將使用 RUNOOB 樣本數據庫。
下面是選自 "Websites" 表的數據:
+----+--------------+---------------------------+-------+---------+ | id | name | url | alexa | country | +----+--------------+---------------------------+-------+---------+ | 1 | Google | https://www.google.cm/ | 1 | USA | | 2 | 淘寶 | https://www.taobao.com/ | 13 | CN | | 3 | 教程 | http://www.sharebody.com/ | 4689 | CN | | 4 | 微博 | http://weibo.com/ | 20 | CN | | 5 | Facebook | https://www.facebook.com/ | 3 | USA | | 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND | +----+---------------+---------------------------+-------+---------+
下面是 "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 實例
復製 "apps" 中的數據插入到 "Websites" 中:
實例
INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps;
SELECT app_name, country FROM apps;
只復 QQ 的 APP 到 "Websites" 中:
實例
INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps
WHERE id=1;
SELECT app_name, country FROM apps
WHERE id=1;