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