TOP

SQL INSERT INTO SELECT

YouLibreCalc for Excel logo

INSERT INTO SELECT 描述

SQL 语句 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”(“客户”)中的示例:

CustomerIDCustomerNameContactNameAddressCityPostalCodeCountry
1Alfreds FutterkisteMaria AndersObere Str. 57Berlin12209Germany
2Ana Trujillo Emparedados y heladosAna TrujilloAvda. de la Constitución 2222México D.F.5021Mexico
3Antonio Moreno TaqueríaAntonio MorenoMataderos 2312México D.F.5023Mexico
4Around the HornThomas Hardy120 Hanover Sq.LondonWA1 1DPUK
5Berglunds snabbköpChristina BerglundBerguvsvägen 8LuleåS-958 22Sweden

以及表“Suppliers”(“供应商”)中的示例:

SupplierIDSupplierNameContactNameAddressCityPostalCodeCountryPhone
1Exotic LiquidCharlotte Cooper49 Gilbert St.LondonaEC1 4SDUK(171) 555-2222
2New Orleans Cajun DelightsShelley BurkeP.O. Box 78934New Orleans70117USA(100) 555-4822
3Grandma Kelly's HomesteadRegina Murphy707 Oxford Rd.Ann Arbor48104USA(313) 555-5735
4Tokyo TradersYoshi Nagase9-8 Sekimai Musashino-shiTokyo100Japan(03) 3555-5011
5Cooperativa de Quesos 'Las Cabras'Antonio del Valle SaavedraCalle del Rosal 4Oviedo33007Spain(98) 598 76 54

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'