sql-server - 从 Excel 工作表将数据插入 SQL 表

Question

如果我在 SQL 中有下表，

Item  Desc   Type   Location    Quantity    Unit_Price

A     AAA     X        1           0          20.00
B     BBB     Y        2           0          10.00
B     CCC     X        2           0          50.00
C     DDD     Z        1           0         150.00
C     EEE     Y        3           0          70.00
D     FFF     Z        3           0          65.00

还有下面的Excel表格

Item    Location   Quantity
A          1           1
B          1           2
B          2           3
C          1          40
C          3         500
D          3          10

如何将这些数量插入到从我的 Excel 表读取的 SQL 表中？

Answer 1

要使用 T-SQL 执行此操作，您可以遵循此 tutorial详细说明并首先将数据拉入临时表，如以下 SELECT…INTO 语句所示:

SELECT * INTO #ProductInfo
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0 Xml; HDR=YES; IMEX=1;
   Database=C:\DataFiles\ProductData.xlsx',
   [vProduct$]);

使用 OPENROWSET用于检索 Excel 数据的内置函数，其中第一个参数是提供者的名称，它可以是两个提供者之一:

• Microsoft.Jet.OLEDB.4.0:在用于 Excel 的 SQL Server 32 位版本上使用2003 文件(或更早版本)。
• Microsoft.ACE.OLEDB.12.0:在 SQL Server 上使用Excel 2007 文件(或更高版本)或 SQL Server 的 32 位版本适用于任何 Excel 文件的 64 位版本。

第二个 OPENROWSET 参数定义由分号分隔的提供程序字符串，第一部分指定文件类型:

• 对于 Excel ’97-2003 (.xls) 文件，请使用 Excel 8.0。
• 对于 Excel 2007-2010 (.xlsx) 文件，请使用 Excel 12.0 Xml。
• 对于 Excel 2007-2010 启用宏的 (.xlsm) 文件，请使用 Excel 12.0宏观。
• 对于 Excel 2007-2010 非 XML 二进制 (.xlsb) 文件，请使用 Excel 12.0。

参数的第二部分指定文件的路径和文件名。第三个参数是我们要访问的电子表格的名称，附加了美元符号 ($) 并括在方括号中，例如 [Products$]。

将数据放入临时表 #ProductInfo 后，您可以根据需要使用内部联接转换、过滤数据，然后更新数量字段:

UPDATE
    p
SET
    p.Quantity = temp.Quantity
FROM dbo.Product AS p
INNER JOIN #ProductInfo AS temp
   ON temp.Item = p.Item
   AND temp.Location = p.Location;