SQL读取Excel数据

有个群友升级到SQL2008后，原本可以正常执行的脚本读取Excel数据的功能忽然不能用了。之前只知道在SSMS里手工导入，看到这个新功能，就决定试试看。

测试过程遇到了以下问题：

• "SQL Server 阻止了对组件 ‘Ad Hoc Distributed Queries’ 的 STATEMENT ‘OpenRowset/OpenDatasource’ 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 ‘Ad Hoc Distributed Queries’。
  解决的办法：

启用Ad Hoc Distributed Queries：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

关闭Ad Hoc Distributed Queries：
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

• 无法创建链接服务器 “(null)” 的 OLE DB 访问接口 “Microsoft.ACE.OLEDB.12.0” 的实例。. Error 7302. SQLSTATE 42000. Severity 16. MsgState 1. Line 1.

    --允许在进程中使用ACE.OLEDB.12
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
    --允许动态参数
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

同时需要安装个AccessDatabaseEngine_X64.exe。

• 另外，运行时，需要关闭相关的Excel文件。
  代码如下：

select * from OpenRowSet('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=Yes;IMEX=1;Database=D:/Excel/DataAll.xlsx', [Sheet1$])  

这个功能在实际工作中的意义

我公司的客户每天会更新他们的产品数据库（Excel版），我们也需要及时更新。为了方便同事使用，我把产品数据导入了SQL，同事们通过MS Query的功能查询。每次更新产品，通常有这几个方法：1）删除原产品表，再手工导入；2）导入到SQL临时表，在SQL里比较、更新；3）在Excel里比较后，导入新产品到SQL；4）写程序比较后导入。
有了这个方法，可以创建一个视图获取Excel数据，再与SQL表比对，导入新记录。

扩展应用是实现多文件的合并。适用于预算、资金计划等需要汇总的场景。