SQL Server 直連 Excel 數據源完整指南：查詢、導入、導出、寫入四大操作

利用 OPENROWSET/OPENDATASOURCE 與 Jet/ACE OLE DB 驅動，SQL Server 可將 Excel 視為關聯數據源，實現無 SSIS/導入導出嚮導的輕量級數據交互。

前置配置：啟用 Ad Hoc 分布式查詢

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;

驅動選擇：

• Microsoft.Jet.OLEDB.4.0：僅支援 .xls (Excel 97-2003)，32 位進程。
• Microsoft.ACE.OLEDB.12.0：支援 .xls/.xlsx，需安裝 Access Database Engine（建議 64 位版匹配 SQL Server）。

操作一：查詢 Excel 數據（不落地）

-- .xls 範例
SELECT * 
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
    'Data Source="C:\Data\Book1.xls";Extended Properties="Excel 8.0;HDR=YES"')
...[Sheet1$];

-- .xlsx 範例（ACE）
SELECT * 
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
    'Data Source="C:\Data\Book1.xlsx";Extended Properties="Excel 12.0 Xml;HDR=YES"')
...[Sheet1$];

關鍵參數：HDR=YES 首行作列名；IMEX=1 混合類型列按文本讀取（避免數字列讀空）。

操作二：將 Excel 導入 SQL Server（建表+灌數據）

SELECT * INTO dbo.NewTable
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
    'Data Source="C:\Data\Book1.xlsx";Extended Properties="Excel 12.0 Xml;HDR=YES"')
...[Sheet1$];

自動推斷列類型，主鍵/索引需後續手動添加。

操作三：將 SQL Server 數據導出到 Excel

方法 A：BCP 命令列（生產環境首選，高效能）

-- 導出表
EXEC master..xp_cmdshell 
'bcp "SELECT Col1, Col2 FROM MyDB.dbo.MyTable" queryout "C:\Export\Data.xlsx" -c -t, -S"ServerName" -U"User" -P"Pwd"';

-- 參數說明：-c 字符模式，-t 分隔符，-S/U/P 連接信息

方法 B：OPENROWSET 寫入（需目標文件預存在且有表頭）

INSERT INTO OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
    'Data Source="C:\Export\Template.xlsx";Extended Properties="Excel 12.0 Xml;HDR=YES"')
...[Sheet1$] (Col1, Col2)
SELECT Col1, Col2 FROM MyDB.dbo.MyTable;

限制：目標 Excel 必須預先存在、工作表有表頭、列順序一致，且不支援自動建文件。

操作四：向 Excel 追加/更新數據

INSERT INTO OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
    'Data Source="C:\Data\Book1.xlsx";Extended Properties="Excel 12.0 Xml;HDR=YES"')
...[Sheet1$] (A1, A2, A3)
VALUES (1, 2, 3);

常見錯誤與解決

最佳實踐建議

1. 生產環境優先 BCP/SSIS，穩定性、效能、錯誤處理完善。
2. 臨時分析、小數據量（<10 萬行）可用 OPENROWSET 快速探查。
3. Excel 列類型不穩定 → 模板文件預設列格式、或 IMEX=1 強制文本讀取。
4. 導出 .xlsx 時建議先生成 .csv 再用工具轉換，避免 ACE 併發寫入鎖定問題。