SQL如何实现将Excel数据导入并批量Update_利用Openrowset技术-SQL-PHP中文网

OpenRowSet 不能直接用于 UPDATE，因其底层 Excel 驱动（如 ACE/OLE DB）仅支持只读操作；所谓“批量更新”实为通过 SELECT OPENROWSET(...) 作数据源，再在 UPDATE ... FROM 中与目标表 JOIN 实现。

sql如何实现将excel数据导入并批量update_利用openrowset技术

OpenRowSet 不能直接用于 UPDATE，它只支持 SELECT；想“批量更新”Excel数据，本质是把 Excel 当成只读源，再用它驱动 SQL Server 的 UPDATE 语句。

为什么 OpenRowSet 无法直接 UPDATE Excel 文件

OpenRowSet 是一个即席分布式查询接口，底层调用 ACE/OLE DB 驱动（如 Microsoft.ACE.OLEDB.12.0），而 Excel 驱动本身不支持写入操作（UPDATE/INSERT/DELETE）。即使语法上看似可行，执行时会报错：The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. 或更常见的：Cannot update a null column. —— 实际是驱动拒绝写入。

所以所谓“用 OpenRowSet 批量 Update”，真实路径是：

用 SELECT * FROM OPENROWSET(...) 把 Excel 数据查出来
把它作为子查询或 CTE，和目标表 JOIN
用 UPDATE ... SET ... FROM ... 语法完成更新

启用 OpenRowSet 前必须做的三件事

SQL Server 默认禁用 Ad Hoc Distributed Queries，且依赖 ACE 驱动。漏掉任一环节都会报 Msg 7415, Level 16, State 1: Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied.

启用外围配置：sp_configure 'show advanced options', 1; RECONFIGURE; sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;
确认服务器已安装 Microsoft Access Database Engine（注意：x64 SQL Server 必须配 x64 ACE 驱动；若装了 32 位 Office，需卸载后装独立 x64 版，否则报错 Could not load library 'ACEOLEDB.DLL'
Excel 文件路径需为数据库服务器本地路径（不是你本机路径），且 SQL Server 服务账户要有读取权限（常被忽略）

UPDATE 语句中正确关联 Excel 数据的写法

核心是把 OPENROWSET 结果当作“临时源表”，通过关键字段（如 ID、编码）与目标表 JOIN。不要试图在 FROM 子句里直接写 UPDATE t SET ... FROM OPENROWSET(...) s —— 这语法非法。

tidy-excel-data

清理、标准化、验证并汇总杂乱的 Excel 工作簿。当 Codex 需要处理 `.xlsx`、`.xls`、`.xlsm` 或 `.csv` 格式的数据时使用，包括：统一表头格式、删除空白行与空白列、修剪文本内容、去重记录、检测数据质量问题、拆分或合并工作表、生成清洗后的工作簿，以及为业务电子表格生成审计摘要。

下载

正确模式是：

UPDATE t
SET t.Price = s.Price,
    t.Stock = s.Stock
FROM dbo.Products t
INNER JOIN (
    SELECT [ID], [Price], [Stock]
    FROM OPENROWSET(
        'Microsoft.ACE.OLEDB.12.0',
        'Excel 12.0;Database=\serversharestock_data.xlsx;HDR=YES',
        'SELECT * FROM [Sheet1$]'
    ) AS s
) AS s ON t.ProductID = s.ID;

注意点：

HDR=YES 表示首行是列名；若没标题，改用 HDR=NO，字段名变成 F1, F2…
Excel 表名必须带 $ 符号（[Sheet1$]），否则报 Could not find installable ISAM
路径建议用 UNC（\servershare...），避免盘符映射问题；若用本地路径（C:...），确保是 SQL Server 所在机器的 C 盘
Excel 列名含空格或特殊字符时，OPENROWSET 内部要用方括号包裹，但外部 SELECT 列别名可简化处理

比 OpenRowSet 更稳的替代方案

OpenRowSet 对 Excel 格式敏感（.xlsx/.xls 混用、日期识别错、空行截断）、无事务、难调试。生产环境建议：

先用 SSIS 或 PowerShell 把 Excel 导入临时表（##temp 或 staging.Products_Import），再用标准 MERGE 或 UPDATE FROM 更新主表
若必须即席导入，改用 BULK INSERT + CSV（Excel 另存为 UTF-8 CSV，用 FORMAT = 'CSV' 参数），性能和稳定性高得多
SQL Server 2019+ 可考虑 OPENJSON 配合 Excel 转 JSON 工具，绕过 OLE DB 限制

真正卡住人的往往不是语法，而是驱动架构匹配、文件权限、路径可见性这三点——调不通时，先 telnet 通 UNC 路径，再用资源管理器以 SQL Server 服务账户身份手动打开那个 Excel 文件。