萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 跨伺服器資料操作全攻略:Linked Server、OPENROWSET、OPENQUERY、OPENDATASOURCE

四大技術對比速查

技術連線持久性語法風格適用場景
Linked Server永久、伺服器級四段式命名 Server.DB.Schema.Table頻繁跨庫查詢、分散式交易、異構資料源
OPENQUERY依賴 Linked Server傳遞遠端原生 SQL 字串複雜邏輯下推、效能優化、Oracle/PostgreSQL 等異構
OPENROWSET臨時、語句級Provider + 連線字串 + SQL/物件一次性匯入/匯出、無法建立 Linked Server 環境
OPENDATASOURCE臨時、語句級Provider + 連線字串 + 物件同 OPENROWSET,連線字串格式更標準 (ADO.NET 風格)

Linked Server 完整生命週期

-- 1. 建立 (SQLNCLI 為推薦 Provider)
EXEC sp_addlinkedserver @server = N'LK_SRV', @srvproduct = N'', @provider = N'SQLNCLI', @datasrc = N'192.168.1.100';
-- 2. 映射登入 (本地登入 -> 遠端登入)
EXEC sp_addlinkedsrvlogin @rmtsrvname = N'LK_SRV', @useself = N'FALSE', @locallogin = NULL, @rmtuser = N'remote_user', @rmtpassword = N'pwd';

-- 3. 使用
SELECT * FROM LK_SRV.DBName.dbo.TableName;
INSERT INTO LocalTable SELECT * FROM LK_SRV.DBName.dbo.TableName;

-- 4. 移除
EXEC sp_dropserver @server = N'LK_SRV', @droplogins = N'droplogins';

OPENROWSET 實戰範例 (SQLNCLI Provider)

-- 單次查詢
SELECT * FROM OPENROWSET('SQLNCLI', 'Server=192.168.1.100;UID=user;PWD=pwd;', 'SELECT Id,Name FROM DB.dbo.Table WHERE Status=1');

-- 匯入建表
SELECT * INTO Local_Table FROM OPENROWSET('SQLNCLI', 'Server=192.168.1.100;UID=user;PWD=pwd;', 'SELECT * FROM DB.dbo.Table');

-- 匯出至遠端 (遠端表需預先存在)
INSERT OPENROWSET('SQLNCLI', 'Server=192.168.1.100;UID=user;PWD=pwd;', 'SELECT Col1,Col2 FROM DB.dbo.TargetTable')
SELECT Col1, Col2 FROM Local_SourceTable;

-- 更新遠端
UPDATE tgt SET tgt.Val = src.Val
FROM OPENROWSET('SQLNCLI', 'Server=192.168.1.100;UID=user;PWD=pwd;', 'SELECT PK, Val FROM DB.dbo.TargetTable') tgt
JOIN Local_SourceTable src ON tgt.PK = src.PK;

OPENQUERY 範例 (需先有 Linked Server)

SELECT * FROM OPENQUERY(LK_SRV, 'SELECT Id, Name FROM DB.dbo.Table WHERE Region=''APAC''');

INSERT OPENQUERY(LK_SRV, 'SELECT Col1, Col2 FROM DB.dbo.TargetTable')
SELECT Col1, Col2 FROM LocalTable;

OPENDATASOURCE 範例

SELECT * FROM OPENDATASOURCE('SQLNCLI', 'Data Source=192.168.1.100;User ID=user;Password=pwd').DB.dbo.Table;

INSERT OPENDATASOURCE('SQLNCLI', 'Data Source=192.168.1.100;User ID=user;Password=pwd').DB.dbo.Table
SELECT * FROM LocalTable;

跨伺服器寫入關鍵陷阱

UPDATE 遠端表時,SET 子句必須使用 FROM 子句中定義的別名,不可直接使用四段式名稱或 OPENROWSET 完整呼叫。

-- 錯誤:會將遠端表所有行的 ColA 更新為本地表第一行的值
UPDATE OPENROWSET(...) SET ColA = (SELECT ColA FROM LocalTable WHERE ...);

-- 正確:使用 JOIN + 別名
UPDATE tgt
SET tgt.ColA = src.ColA
FROM OPENROWSET(...) tgt
JOIN LocalTable src ON tgt.PK = src.PK;

安全與效能清單

  • 啟用 Ad Hoc Distributed Queries 僅在需要時開啟,用畢即關。
  • 遠端帳號最小權限:db_datareader/db_datawriter 於特定資料庫。
  • 優先 OPENQUERY 下推篩選/聯結,減少跨網路傳輸。
  • 大量資料移動改用 SSIS / BCP / 複寫 / AlwaysOn 可讀次要複本。
  • 防火牆限制 1433/瀏覽器埠僅允許應用伺服器 IP。
copyright © 萬盛學電腦網 all rights reserved