轻松上手,快乐学习!

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';

运行实例»