SQL Server 與 Access、Excel 資料互通完整指南

背景與常見場景

企業歷史資料常散落在 Access (.mdb/.accdb) 或 Excel (.xls/.xlsx) 檔案中。升級至 SQL Server 後，仍需定期從這些檔案型資料源抽取、同步或回寫資料。本文整理三大主流技術——分散式查詢、DTS/SSIS、BCP——並提供即用的 T-SQL 範本。

核心技術對照

分散式查詢實戰範本

1. 存取 Access

-- 查詢
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Data Source=C:\Data\db.accdb;', 'SELECT * FROM serv_user');

-- 寫入 (需目標表存在)
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Data Source=C:\Data\db.accdb;', 'SELECT * FROM AccessTable')
SELECT * FROM SQLTable;

-- 刪除/更新
DELETE FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Data Source=C:\Data\db.accdb;', 'SELECT * FROM serv_user') WHERE lock = 0;

UPDATE OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Data Source=C:\Data\db.accdb;', 'SELECT * FROM serv_user')
SET lock = 1;

注意：64 位元 SQL Server 必須安裝 64 位元 Access Database Engine (ACE.OLEDB.12.0/16.0)，舊版 Jet OLEDB 4.0 僅支援 32 位元。

2. 存取 Excel

-- 查詢 (HDR=YES 表頭視為欄位名，工作表名需加 $ 並用方括號)
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=YES;Database=C:\Data\book.xlsx;', 'SELECT * FROM [Sheet1$]')
WHERE [ClientName] LIKE '%台積%';

-- 匯入建新表
SELECT * INTO NewTable FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=YES;Database=C:\Data\book.xlsx;', 'SELECT * FROM [Sheet1$]');

-- 匯出到既有 Excel (需先建立工作表與表頭)
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=YES;Database=C:\Data\book.xlsx;', 'SELECT * FROM [Sheet1$]')
SELECT * FROM SQLTable;

BCP 命令列範例

REM 匯出資料表至文字檔 (逗點分隔、含表頭)
bcp "SELECT * FROM Db.dbo.Table" queryout "C:\Export\data.csv" -c -t, -T -S.

REM 匯入文字檔至資料表 (需格式檔或目標表結構相符)
bcp Db.dbo.Table in "C:\Import\data.csv" -c -t, -T -S. -h "TABLOCK"

關鍵參數：-c 字元模式、-t, 欄位分隔符、-T 信任連線、-h "TABLOCK" 大量匯入鎖定優化。

權限與安全性設定清單

• 啟用分散式查詢：EXEC sp_configure 'show advanced options',1; RECONFIGURE; EXEC sp_configure 'Ad Hoc Distributed Queries',1; RECONFIGURE;
• 服務帳號需具備檔案系統讀寫權限 (UNC 路徑建議用網域帳號)。
• Excel/Access 檔案不可被其他程式獨佔開啟。
• 生產環境建議建立連結伺服器集中管理連線字串與認證，避免連線字串散落在程式碼中。

現代化替代方案

SQL Server 2016+ 內建 OPENJSON、PolyBase，Azure 資料工廠 (ADF) 提供無伺服器 ETL。若資料量大、頻率高、來源雲端化，建議逐步遷移至 SSIS/ADF/PolyBase 架構，降低維運複雜度。