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语句仅将 Germany 供应商复制到“Customers”中：
实例
INSERT INTO Customers (CustomerName, City, Country)
SELECT SupplierName, City, Country FROM Suppliers
WHERE Country='Germany';
运行实例»
