您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 與 Access、Excel 資料互通三大經典方案總結 (OPENROWSET、OPENDATASOURCE、BCP)

編輯：關於SqlServer

背景動機

歷史系統資料常存於 Access/Excel/FoxPro，升級至 SQL Server/Oracle 後仍需頻繁存取舊格式資料。本文總結三大原生方案。

方案一：分散式查詢 - OPENROWSET (Ad Hoc)

Access 範例


-- 查詢 Access 資料
SELECT * FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    ';Database=C:\db2.mdb',
    'SELECT * FROM serv_user'
);

-- 或指定完整連線字串
SELECT * FROM OPENDATASOURCE(
    'Microsoft.Jet.OLEDB.4.0',
    'Data Source="C:\db2.mdb";User ID=Admin;Password='
)...serv_user;

寫入 Access (SQL Server → Access)


INSERT INTO OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    ';Database=C:\db2.mdb',
    'SELECT * FROM AccessTable'
)
SELECT * FROM SqlServerTable;

讀取 Access 寫入 SQL Server (Access → SQL Server)


INSERT INTO SqlServerTable
SELECT * FROM OPENROWSET(
    'Microsoft.Jet.OLEDB.4.0',
    ';Database=C:\db2.mdb',
    'SELECT * FROM AccessTable'
);

方案二：BCP 工具 (大量複製程式)


-- SQL Server → Access (自動建立 mdb 若不存在)
EXEC master..xp_cmdshell 'bcp "DBName.dbo.TableName" OUT "C:\db3.mdb" -c -q -S"." -U"sa" -P"sa"'

-- Access → SQL Server
EXEC master..xp_cmdshell 'bcp "DBName.dbo.TableName" IN "C:\db3.mdb" -c -q -S"." -U"sa" -P"sa"'

OPENROWSET vs BCP 關鍵差異

特性	OPENROWSET	BCP
目標檔案/表需預先存在	是	否 (自動建立)
效能 (大量資料)	中等	高 (批次、無記錄選項)
權限需求	Ad Hoc Distributed Queries 啟用	xp_cmdshell 啟用、OS 層級檔案權限
錯誤處理	T-SQL TRY-CATCH	回傳碼、需解析輸出

方案三：連結伺服器 (Linked Server) - 生產環境推薦


-- 一次性建立
EXEC sp_addlinkedserver 
    @server = 'LK_ACCESS', 
    @srvproduct = 'Access',
    @provider = 'Microsoft.Jet.OLEDB.4.0',
    @datasrc = 'C:\db2.mdb';

EXEC sp_addlinkedsrvlogin 'LK_ACCESS', 'false', NULL, 'Admin', '';

-- 之後像查本地表一樣查詢
SELECT * FROM LK_ACCESS...serv_user;
INSERT INTO LK_ACCESS...AccessTable SELECT * FROM SqlServerTable;

Excel 專屬連線字串參考

Excel 版本	Provider	Extended Properties
Excel 97-2003 (.xls)	Microsoft.Jet.OLEDB.4.0	Excel 8.0;HDR=YES;IMEX=1
Excel 2007+ (.xlsx)	Microsoft.ACE.OLEDB.12.0	Excel 12.0;HDR=YES;IMEX=1


-- 查詢 Excel (OpenRowSet)
SELECT * FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;Database=C:\data.xlsx;HDR=YES;IMEX=1',
    'SELECT * FROM [Sheet1$]'
);

必要伺服器設定


-- 啟用 Ad Hoc Distributed Queries (OPENROWSET/OPENDATASOURCE 需要)
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;

-- 啟用 xp_cmdshell (BCP 需要)
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;