SQL Server 與 Access/Excel 資料互轉完整指南：OPENDATASOURCE、OPENROWSET 與 DTS 三大法

三大互轉方法比較

方法一：DTS 匯入/匯出精靈 (SQL 2000) / SSIS (2005+)

1. 企業管理員 → 工具 → 資料轉換服務 → 匯入資料。
2. 資料來源：選 Microsoft Access / Excel。
3. 目的地：Microsoft OLE DB Provider for SQL Server。
4. 選擇「複製資料表」或「寫入查詢結果」。
5. 完成 → 可另存為 DTS 套件 / SSIS 專案供排程執行。

方法二：OPENDATASOURCE 臨時存取

查詢 Access

SELECT * 
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 
    'Data Source="C:\DB.mdb";User ID=Admin;Password='')...TableName;

Access 匯入 SQL Server (建立新表)

SELECT * INTO NewTable
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 
    'Data Source="C:\DB.mdb";User ID=Admin;Password='')...TableName;

SQL Server 資料寫入 Access

INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 
    'Data Source="C:\DB.mdb";User ID=Admin;Password='')...TableName (Col1, Col2)
SELECT Col1, Col2 FROM SqlTable;

查詢 Excel

SELECT * 
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 
    'Data Source="C:\Book1.xls";User ID=Admin;Password=;Extended Properties=Excel 8.0')...[Sheet1$];

注意：工作表名稱需加 $ 並用方括號包裹 [Sheet1$]；HDR=YES 表示第一列為標題。

方法三：OPENROWSET (功能更強)

查詢 Excel 範例

SELECT * 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=C:\Finance\Account.xls;HDR=YES', 'SELECT * FROM [Sheet1$]');

Access 匯入 SQL Server

SELECT * INTO NewTable
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'C:\DB.mdb'; 'admin'; '', TableName);

SQL Server 寫入 Access

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'C:\Trade.mdb'; 'admin'; '', TableName)
SELECT * FROM SqlTable;

64 位元環境關鍵設定

• 安裝 Microsoft Access Database Engine 2010/2016 Redistributable (64-bit)。
• Provider 字串改用 Microsoft.ACE.OLEDB.12.0 (支援 .xlsx/.accdb)。
• 若同時安裝 32/64 位元 Office，可能導致 Provider 註冊衝突，建議純 64 位元環境或使用 SSIS 32 位元執行模式。

權限與安全

• 執行帳號需具備 ADMINISTER BULK OPERATIONS 或 sysadmin (OPENROWSET BULK)。
• 檔案路徑需為 SQL Server 服務帳號可存取的本機路徑或 UNC 路徑。
• 生產環境建議：建立連結伺服器集中管理連接字串，授權特定登入存取。

結語

SQL Server 與 Office 檔案互轉，臨時查詢用 OPENDATASOURCE/OPENROWSET 最快；定期 ETL 用 SSIS 最穩健；頻繁存取用連結伺服器最安全。根據頻率、資料量、自動化需求選擇適合工具。