SQL Insert Into Select

---

SQL INSERT INTO SELECT 语句

INSERT INTO SELECT 语句从一个表复制数据并将其插入另一个表。

• INSERT INTO SELECT 要求源表和目标表中的数据类型匹配
• 目标表中的现有记录不受影响

INSERT INTO SELECT 语法

将所有列从一个表复制到另一个表:

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

仅将一个表中的某些列复制到另一个表中:

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

---

演示数据库

在本教程中，我们将使用著名的 Northwind 示例数据库。

以下是从 "Customers" 表中选择的内容:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico

以及从 "Suppliers" 表中选择的内容:

SupplierID	SupplierName	ContactName	Address	City	Postal Code	Country
1	Exotic Liquid	Charlotte Cooper	49 Gilbert St.	Londona	EC1 4SD	UK
2	New Orleans Cajun Delights	Shelley Burke	P.O. Box 78934	New Orleans	70117	USA
3	Grandma Kelly's Homestead	Regina Murphy	707 Oxford Rd.	Ann Arbor	48104	USA

---

---

SQL INSERT INTO SELECT 实例

以下SQL语句将"Suppliers"复制到"Customers"中（未填充数据的列将包含 NULL）:

实例

INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers; 亲自试一试 »

以下SQL语句将"Suppliers"复制到"Customers"中（填写所有列）:

实例

INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
SELECT SupplierName, ContactName, Address, City, PostalCode, Country FROM Suppliers; 亲自试一试 »

以下SQL语句仅将德国供应商复制到"Customers"中:

实例

INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany'; 亲自试一试 »