[SQL] Insert Into Select (使用 Select語法的 Insert)

INSERT INTO SELECT

當要新增從別的資料表取得的資料時，用Select語法來取得Values敘述比較好處理。

語法一：省略欄位

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

語法二：指定所有欄位

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

範例一：(指定所有欄位)

將分店負責人清單中負責人id為10,11的資料，新增到負責人清單中

--將分店負責人清單中負責人id為10,11的資料，新增到負責人清單中
insert into 負責人清單(負責人ID,負責人姓名,姓名拼音,MGR_ID,出生日期,性別)
select 分店負責人ID,分店負責人姓名,姓名拼音,MGR_ID,出生日期,性別 
from 分店負責人清單
where 分店負責人ID in(10,11)

Copy “Suppliers” into “Customers” (fill all columns):

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers;

範例二：(省略所有欄位)

將分店負責人清單中負責人id為10,11的資料，新增到負責人清單中

--將分店負責人清單中負責人id為10,11的資料，新增到負責人清單中
insert into 負責人清單
select * from 分店負責人清單
where 分店負責人ID in(12,13)

範例三：(省略部分欄位)

Copy “Suppliers” into “Customers” (the columns that are not filled with data, will contain NULL):

INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers;

Copy only the German suppliers into “Customers”:

INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany';