| 方法 | 適用場景 | 優點 | 缺點 |
|---|---|---|---|
OPENROWSET | 臨時、單次導入導出 | 無需鏈接服務器、語法靈活 | 需啟用 Ad Hoc Distributed Queries |
OPENDATASOURCE | 簡單查詢、小數據量 | 語法簡潔 | 不支持參數化、性能一般 |
| 鏈接服務器 | 頻繁、生產環境互訪 | 安全、可管理性強、支持分布式事務 | 配置較繁瑣 |
BCP / BULK INSERT | 大批量數據載入/導出 | 極高性能、流式處理、少日誌 | 需文件系統訪問、格式文件複雜 |
| SSIS / DTS | 複雜 ETL、異構數據源 | 可視化、工作流、錯誤處理豐富 | 學習曲線陡峭、部署重 |
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;
-- Excel 2007+ (.xlsx) 使用 ACE 提供程序
SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Data\sales.xlsx;HDR=YES;IMEX=1',
'SELECT * FROM [Sheet1$]'
);
-- Excel 97-2003 (.xls) 使用 Jet 提供程序
SELECT * FROM OPENROWSET(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Data\old.xls;HDR=YES;IMEX=1',
'SELECT * FROM [Sheet1$]'
);
關鍵參數:
HDR=YES:第一行是列名(默認)。HDR=NO 則視為數據。IMEX=1:混合類型列按文本處理,避免數字列被誤判為浮點而截斷文本。[Sheet1$](含 $ 符號,方括號括起)。SELECT * INTO dbo.Sales_Import
FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Data\sales.xlsx;HDR=YES;IMEX=1',
'SELECT * FROM [Sheet1$]'
);
INSERT INTO dbo.Sales (OrderID, Customer, Amount, OrderDate)
SELECT OrderID, Customer, Amount, OrderDate
FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Data\sales.xlsx;HDR=YES;IMEX=1',
'SELECT * FROM [Sheet1$]'
);
-- 方法一:OPENROWSET(需目標文件預先存在且有表頭)
INSERT INTO OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Data\export.xlsx;HDR=YES',
'SELECT * FROM [Sheet1$]'
)
SELECT OrderID, Customer, Amount, OrderDate FROM dbo.Sales;
注意:目標 Excel 必須預先創建好 Sheet1 並寫好表頭,否則報錯。
-- 導出為 CSV(Excel 可直接打開)
EXEC master..xp_cmdshell 'bcp "SELECT OrderID, Customer, Amount, OrderDate FROM SalesDB.dbo.Sales" queryout "C:\Export\sales.csv" -c -t, -T -S localhost';
-- 參數說明:
-- -c 字符模式
-- -t, 字段分隔符(逗號)
-- -T 可信連接(Windows 驗證)
-- -S 服務器實例
-- 查詢 Access
SELECT * FROM OPENROWSET(
'Microsoft.ACE.OLEDB.12.0',
'Database=C:\Data\app.accdb;',
'SELECT * FROM Customers'
);
-- 導入 Access
SELECT * INTO dbo.Cust_Import
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Database=C:\Data\app.accdb;', 'Customers');
-- SQL Server 寫入 Access
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Database=C:\Data\app.accdb;', 'Customers')
SELECT CustID, Name, Email FROM dbo.Customers;
| 錯誤現象 | 原因 | 解決 |
|---|---|---|
OLE DB provider 'Microsoft.ACE.OLEDB.12.0' not registered | 未安裝 Access Database Engine | 下載安裝 Microsoft Access Database Engine 2016 Redistributable(選 64 位匹配 SQL Server 位數) |
The Microsoft Access database engine could not find the object 'Sheet1$' | 工作表名錯誤或不存在 | 在 Excel 中確認工作表名,含 $ 且用 [] 括起 |
Ad Hoc Distributed Queries disabled | 未啟用該選項 | 執行上文 sp_configure 啟用 |
Cannot create an instance of OLE DB provider | 權限不足 / 64/32 位不匹配 | 確保 SQL Server 服務賬號有文件讀寫權限;安裝 64 位 ACE |
| 數據類型不匹配/截斷 | Excel 混合類型列推斷錯誤 | 加 IMEX=1;或預先在 Excel 設為文本格式 |
-- Delphi (ADO)
ADOQuery1.SQL.Text :=
'INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;Database=' + XlsPath + ''', ''SELECT * FROM [Sheet1$]'') ' +
'SELECT ID, Name, Amount FROM SQLTable';
ADOQuery1.ExecSQL;
OPENROWSET + ACE Provider,開箱即用。BCP 導出 CSV → Python/PowerShell 轉 Excel,或 SSIS 包。Excel 不是數據庫!高頻、大量、高併發的數據交互請使用正規關系數據庫或數據倉庫。Excel 互導僅適用於人工操作、報表分發、遷移上線等低頻場景。