原創筆記：使用T-SQL語句将excel表資料寫入SQL server的實作方式

今天有朋友問我，如何在MS SQL server中如何用SQL語句操作Excel資料的問題。好久沒用SQL了，都快忘記了，趕緊複習了一下，并成功幫朋友解決了他想要的效果。

本文由個人純原創，非常詳細的筆記，希望能給朋友們幫助！如有bug或有更好的方法，歡迎斧正！評論區讨論或私聊！謝謝關注！

我的筆記本【windows 11】安裝的是MS SQL Server 2014版本為例。

目前PC安裝的版本

剛開始，我直接用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前務必将檔案關閉。

excel資料展示

執行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更新後的資料,如圖

預覽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