SQL Server 與 Access、Excel 資料互轉：DTS 精靈與 T-SQL OPENROWSET 兩大方案

方案一：DTS 匯入/匯出精靈 (圖形化)

1. 企業管理器 → 工具 → 資料轉換服務 → 匯入資料。
2. 資料來源：選 Microsoft Access，指定 .mdb 檔案路徑。
3. 目的地：選 Microsoft OLE DB Provider for SQL Server，指定伺服器、驗證方式、目標資料庫。
4. 指定表格複製：選 複製資料表 → 全選 → 完成。

同樣支援 Excel、文字檔、Oracle 等任意 OLE DB 來源。

方案二：T-SQL OPENROWSET / OPENDATASOURCE (指令碼化)

適用於自動化腳本、預存程序、排程作業，無需 GUI 操作。

1. 查詢 Access 資料

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

2. Access 匯入 SQL Server (建立新表)

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

3. SQL Server 資料寫入 Access

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

4. Excel 互轉 (Provider: Microsoft.Jet.OLEDB.4.0 + Extended Properties)

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

-- Excel 匯入 SQL
SELECT * INTO NewTable FROM OPENDATASOURCE(...)...[Sheet1$]

-- SQL 匯出 Excel (需預先建立檔案與工作表標題)
INSERT INTO OPENDATASOURCE(...)...[Sheet1$]
SELECT col1, col2 FROM LocalTable

關鍵 Provider 字串

前置條件

• 啟用 Ad Hoc Distributed Queries：
  EXEC sp_configure 'show advanced options',1; RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries',1; RECONFIGURE;
• 目標機器安裝對應 OLE DB Provider (Jet/ACE)。
• 檔案路徑為 SQL Server 服務帳號可存取的本地或 UNC 路徑。

方案選擇建議

• 一次性、互動式、少量資料 → DTS 精靈。
• 自動化、排程、嵌入預存程序、大量資料 → T-SQL OPENROWSET。