原创笔记:使用T-SQL语句将excel表数据写入SQL server的实现方式

今天有朋友问我，如何在MS SQL server中如何用SQL语句操作Excel数据的问题。好久没用SQL了，都快忘记了，赶紧复习了一下，并成功帮朋友解决了他想要的效果。

本文由个人纯原创，非常详细的笔记，希望能给朋友们帮助！如有bug或有更好的方法，欢迎斧正！评论区讨论或私聊！谢谢关注！

我的笔记本【windows 11】安装的是MS SQL Server 2014版本为例。

刚开始，我直接用sql语句执行，系统报错。

消息 7399……链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.ACE.OLEDB.12.0" 报错。访问被拒绝。

消息 7301……无法从链接服务器 "(null)" 的 OLE DB 访问接口 "Microsoft.ACE.OLEDB.12.0" 获取所需的界面("IID_IDBCreateCommand")。

消息 7302……无法创建链接服务器 "(null)" 的 OLE DB 访问接口 "MSDASC" 的实例

所以, 咱必须做以下操作。

1、打开 服务-> 找到 SQL Server 服务->右键 -> 登录 -> 选择 "本地系统账户"。应用后重启服务。

2、打开SQL Server 管理器【用当前本地windows账户登录】

然后找到服务器对象->链接服务器->找到Microsoft.ACE.OLEDB.12.0【执行xlsx, xls】

补充一下：

A、Microsoft.Jet.OLEDB.4.0 （仅支持.xls文件，office2003）

B、microsoft.ACE.oledb.12.0(支持xlsx, xls文件均支持,也就是office2007以后的版本32/64位)

C、microsoft.ACE.oledb.16.0(支持xlsx, xls文件均支持,也就是office2016以后的版本64位)

对于今天这个，我只用microsoft.ACE.oledb.12.0操作excel即可。好了，现在就开始我的代码之旅吧。

一、使用OPENDATASOURCE前需要确保该服务开启:

    exec sp_configure 'show advanced options',1
    reconfigure
    exec sp_configure 'Ad Hoc Distributed Queries',1
    reconfigure
 
    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
    go

二、相关SQL操作EXCEL语句

实例：将文件放在D:\Temp\my.xlsx, 数据如图所示

执行SQL前务必将文件关闭。

执行SQL语句，将excel的数据插入一个新表（#tmp_db）并且select查询出来。

效果如图。效果完美。！

drop table #tmp_db
select * into #tmp_db
from OpenDataSource
('Microsoft.ACE.OLEDB.12.0','Data Source=d:\Temp\my.xlsx;Extended Properties=Excel 12.0')
...[Sheet1$];
go
select * from #tmp_db
go

其实操作语句有好几种。个人测试有以下几种

OpenDataSource和OpenRowSet，个人觉得OpenRowSet性能更好，可读性更强

drop table #tmp
CREATE TABLE #tmp
(
ACCOUNTNUM Nvarchar(60),
TESTID Nvarchar(60),
CONFIGID Nvarchar(60),
ITEMID Nvarchar(255)
);

insert into #tmp(ACCOUNTNUM,TESTID,CONFIGID,ITEMID)
(
select e.ACCOUNTNUM,e.TESTID,e.CONFIGID,e.ItemId 
from OpenRowSet
('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=d:\Temp\my.xlsx', [Sheet1$]) as e
)
select * from #tmp
go

--方法1
select * from OpenRowSet
('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=d:\Temp\my.xlsx', [Sheet1$]
);
go
---方法2
select * from OpenRowSet
('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=d:\Temp\my.xlsx', 'SELECT * FROM [Sheet1$]') AS E 
go
--方法3
select * from OpenDataSource
('Microsoft.ACE.OLEDB.12.0','Data Source=d:\Temp\my.xlsx;Extended Properties=Excel 12.0')
...[Sheet1$];
go

那么如果插入或更新已存在表结构的表数据呢。

2-1）批量插入已存在表结构

语句如下：批量从EXCEL数据源中查询的结果插入到指定表#tmp的指定列，

注意：将excel数据插入到数据表，进行数据加工处理，注意查询出来的列和表列数需一致.

--insert into test.dbo.test(字段名A,B,C,D) --必须和EXCEL列名一致.

insert into #tmp(ACCOUNTNUM,TESTID,CONFIGID,ITEMID)
(
select e.ACCOUNTNUM,e.TESTID,e.CONFIGID,e.ItemId 
from OpenRowSet
('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=d:\Temp\my.xlsx', [Sheet1$]) as e
)
select * from #tmp
go

2-2）批量更新数据。

我将TESTID列值更新了。预览excel更新后的数据,如图

--更新#tmp数据
update #tmp
set ACCOUNTNUM = E.ACCOUNTNUM,
    TESTID = e.TESTID,
    CONFIGID = e.CONFIGID
from OpenRowSet('Microsoft.ACE.OLEDB.12.0','Excel 12.0; Database=d:\Temp\my.xlsx', 'SELECT * FROM [Sheet1$]') as E 
INNER JOIN #tmp as d on d.ITEMID = E.ITEMID
select * from #tmp
go

完美！！！！2023-03-29 凌晨00:10