使用 OPENDATASOURCE 將 Excel、Access 資料直接匯入 SQL Server

方法一：匯入 Excel (.xls/.xlsx)

-- 查詢並直接插入目標表
SELECT * INTO TargetTable
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
    'Data Source="C:\Data\Source.xls";User ID=Admin;Password=;Extended Properties="Excel 8.0;HDR=YES;IMEX=1"'
)...[Sheet1$];

關鍵參數：

• HDR=YES：第一列為欄位名。
• IMEX=1：混合型別欄位以文字模式讀取，避免數值截斷。
• 工作表名需加 $ 並用方括號括起：[Sheet1$]。
• .xlsx 需改用 Microsoft.ACE.OLEDB.12.0 Provider 並將 Extended Properties 設為 "Excel 12.0;HDR=YES;IMEX=1"。

方法二：匯入 Access (.mdb/.accdb)

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

-- .accdb (Access 2007+)
SELECT * INTO TargetTable
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
    'Data Source="C:\Data\Source.accdb";'
)...SourceTableName;

方法三：建立連結伺服器 (適合重複使用)

-- 建立連結伺服器指向 Excel
EXEC sp_addlinkedserver
    @server = 'LINK_EXCEL',
    @srvproduct = '',
    @provider = 'Microsoft.Jet.OLEDB.4.0',
    @datasrc = 'C:\Data\Source.xls',
    @provstr = 'Extended Properties="Excel 8.0;HDR=YES;IMEX=1"';

-- 查詢
SELECT * FROM LINK_EXCEL...[Sheet1$];

-- 刪除連結伺服器
EXEC sp_dropserver 'LINK_EXCEL', 'droplogins';

注意事項與最佳實踐

• 需啟用 Ad Hoc Distributed Queries：EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;
• 64 位元 SQL Server 需安裝 64 位元 ACE/OLEDB Provider，否則會報「找不到提供者」。
• 大量資料建議先匯入暫存表 (Staging Table)，再經 ETL 清洗轉換載入正式表。
• Excel 列數限制：.xls 65,536 列，.xlsx 1,048,576 列；超過請分檔或改用 SSIS/BCP。
• 權限：SQL Server 服務帳戶需具備來源檔案讀取權限 (網路路徑需網域帳戶)。