您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

SQL Server 異構資料操作全攻略：OPENROWSET/OPENQUERY/OPENDATASOURCE 實戰與 Excel/Access/TXT 導入導出

編輯：Oracle 資料庫基礎

核心函數對比：三大異構存取利器

函數	適用場景	語法特點
`OPENROWSET`	臨時、單次存取遠端/檔案資料	支援完整連線字串，可直接在 FROM/INSERT/UPDATE/DELETE 中使用
`OPENQUERY`	連結伺服器已定義，需傳遞查詢字串	需先建立連結伺服器，查詢在遠端執行，減少網路傳輸
`OPENDATASOURCE`	臨時存取，類似 OPENROWSET 但語法更簡潔	僅支援 SELECT/INSERT，不支援 UPDATE/DELETE

遠端 SQL Server/Oracle/Access 實戰範例

OPENROWSET 完整 CRUD

-- 查詢
SELECT * FROM OPENROWSET('SQLOLEDB', '遠端主機';'sa';'密碼', '資料庫.dbo.表名');

-- 新增
INSERT INTO OPENROWSET('SQLOLEDB', 'BERRY';'sa';'sa', 'tt.dbo.ALaw_BestbigPunish') (OneName) VALUES ('c');

-- 更新
UPDATE OPENROWSET('SQLOLEDB', 'BERRY';'sa';'sa', 'tt.dbo.ALaw_BestbigPunish') SET OneName = 'b';

-- 刪除
DELETE FROM OPENROWSET('SQLOLEDB', 'BERRY';'sa';'sa', 'tt.dbo.ALaw_BestbigPunish');

注意：連線字串分隔符為分號 ; 而非逗號。

OPENQUERY 跨異構平台查詢

-- 連結伺服器 oradb 指向 Oracle
SELECT * FROM OPENQUERY(oradb, 'SELECT * FROM scott.emp');

-- 連結伺服器 SQLOLEDB 指向 SQL Server
SELECT * FROM OPENQUERY(SQLOLEDB, 'SELECT * FROM tt.dbo.ALaw_FilePunish');

OPENDATASOURCE 快速存取

SELECT * FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=192.168.0.111;User ID=sa;Password=sa').tt.dbo.ALaw_BestbigPunish;

Excel 檔案導入導出

-- 直接查詢 Excel 工作表
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\test.xls', 'sheet1$');

-- 導入建表
SELECT * INTO ydy FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=D:\test.xls', 'sheet1$');

-- OPENDATASOURCE 寫法
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source="D:\test.xls";Extended Properties=Excel 8.0')...sheet1$;

關鍵點：工作表名需加 $ 後綴（如 sheet1$），且首行必須為欄位名。

Access 資料庫存取

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\db.mdb';'admin';'', 'SELECT * FROM vote');
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\db.mdb')...vote;

文字檔（TXT/CSV）處理

-- 查詢
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Text;Database=D:\', 'sopendata#txt');

-- 導出（檔案需預存在、首行含欄位名）
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Text;Database=D:\', 'sopendata#txt') SELECT * FROM t1;

命名規則：檔名句點以 # 代替（如 sopendata.txt → sopendata#txt）。

BCP 大量複製工具實戰

-- 導入
EXEC master..xp_cmdshell 'bcp "aa.dbo.ydy" in D:\dbo.txt -c -T -S servername';

-- 導出
EXEC master..xp_cmdshell 'bcp "aa.dbo.ydy" out c:\DT.txt -c -T -S servername';

-- 查詢導出
EXEC master..xp_cmdshell 'bcp "SELECT * FROM dbname..tablename" queryout c:\DT.txt -c -T -S servername';

參數說明：-c 字元模式、-T 信任連線、-S 指定伺服器。