SQL Server 與 Access、Excel 資料互轉實戰：DTS 與 T-SQL 雙軌並行

資料遷移與異構系統整合是 DBA 常態工作。SQL Server 2000 內建的 Data Transformation Services (DTS) 提供圖形化精靈，適合一次性或排程匯入；而透過 OPENROWSET、OPENDATASOURCE 等分散式查詢函數，則能在預存程序、觸發器或腳本中即時存取 Access、Excel 資料，實現無縫整合。本文說明兩大主流管道的操作要領與常見坑點。

管道一：DTS 精靈 — 視覺化批次遷移

Access → SQL Server 完整步驟

1. 開啟 Enterprise Manager，展開「工具」→「資料轉換服務」→「匯入資料」。
2. 資料來源選 Microsoft Access，指定 .mdb 檔案路徑。
3. 目的地選 Microsoft OLE DB Provider for SQL Server，輸入目標伺服器、驗證模式與資料庫名稱。
4. 選擇「複製資料表」模式，於來源資料表清單點選「全選」或勾選特定表。
5. 完成精靈，DTS 封裝即建立完成；可另存為 .dts 檔或排程至 SQL Server Agent 定期執行。

Excel → SQL Server 注意事項

• 資料來源選 Microsoft Jet 4.0 OLE DB Provider，擴充屬性設 Excel 8.0（.xls）或 Excel 12.0（.xlsx）。
• 工作表名稱需加尾碼 $（如 [Sheet1$]），命名範圍則直接使用範圍名稱。
• 混合資料型態欄位：Jet 引擎以前 8 列推斷型態，建議先在 Excel 整理格式或使用 IMEX=1 強制視為文字。

管道二：T-SQL 分散式查詢 — 即時存取與自動化

OPENROWSET 單次即時查詢

-- 直接查詢 Access 資料表
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'C:\Data\Source.mdb';'admin';'', TableName)

-- 匯入 Excel 工作表
SELECT * INTO SQLTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;Database=C:\Data\Source.xls;HDR=YES;IMEX=1',
    'SELECT * FROM [Sheet1$]')

OPENDATASOURCE 建立臨時連結伺服器

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

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

永久連結伺服器（適合高頻存取）

EXEC sp_addlinkedserver
    @server = 'ACC_LINK',
    @provider = 'Microsoft.Jet.OLEDB.4.0',
    @datasrc = 'C:\Data\Source.mdb';

-- 使用四部份命名
SELECT * FROM ACC_LINK...TableName

常見問題與解決方案

選型建議

• DTS/SSIS：大量資料初始載入、複雜轉換邏輯、需視覺化監控、排程執行。
• T-SQL 分散式查詢：即時查詢少量資料、觸發器同步、動態參數化存取、輕量級 ETL 腳本。

掌握雙管道並依場景切換，即能高效完成 SQL Server 與 Office 系列資料的雙向流動。