SQL Server 跨伺服器資料操作四大語法：Linked Server、OPENROWSET、OPENQUERY、OPENDATASOURCE

跨伺服器查詢、同步、遷移是 DBA 與開發常見需求。SQL Server 提供四種主要語法，各有適用場景與優缺點。本文系統整理建立連結、查詢、匯入、更新、刪除連結的完整範例，並特別說明 OPENROWSET/OPENDATASOURCE 無法直接 SELECT INTO 建表的限制與替代方案。

1. Linked Server（永久連結，適合頻繁存取）

-- 建立
EXEC sp_addlinkedserver 'LNK', '', 'SQLOLEDB', 'RemoteIP_or_Name';
EXEC sp_addlinkedsrvlogin 'LNK', 'false', null, 'RemoteUser', 'RemotePwd';

-- 查詢
SELECT * FROM LNK.DBName.dbo.TableName;

-- 匯入建表
SELECT * INTO LocalTable FROM LNK.DBName.dbo.TableName;

-- 更新本地表（以遠端為主）
UPDATE Local
SET Local.ColA = Remote.ColA
FROM LNK.DBName.dbo.TableName Remote
INNER JOIN LocalTable Local ON Remote.KeyCol = Local.KeyCol;

-- 移除
EXEC sp_dropserver 'LNK', 'droplogins';

2. OPENROWSET（臨時、單次、無需建立連結）

-- 查詢
SELECT * FROM OPENROWSET('SQLOLEDB', 'RemoteIP'; 'User'; 'Pwd', DBName.dbo.TableName);

-- 匯入建表（需先建表，再 INSERT）
-- 注意：SELECT INTO OPENROWSET(...) 會報錯，不支援直接建表
SELECT * INTO LocalTable FROM OPENROWSET('SQLOLEDB', 'RemoteIP'; 'User'; 'Pwd', DBName.dbo.TableName); -- 失敗

-- 正確做法：先建表，再 INSERT
INSERT INTO OPENROWSET('SQLOLEDB', 'RemoteIP'; 'User'; 'Pwd', DBName.dbo.TableName)
SELECT * FROM LocalTable;

-- 更新遠端表
UPDATE Remote
SET Remote.ColB = Local.ColB
FROM OPENROWSET('SQLOLEDB', 'RemoteIP'; 'User'; 'Pwd', DBName.dbo.TableName) Remote
INNER JOIN LocalTable Local ON Remote.KeyCol = Local.KeyCol;

3. OPENQUERY（需 Linked Server，效能較好，支援遠端完整 T-SQL）

-- 前提：已建立 Linked Server 'LNK'
-- 查詢
SELECT * FROM OPENQUERY(LNK, 'SELECT * FROM DBName.dbo.TableName');

-- 匯入遠端資料
INSERT INTO OPENQUERY(LNK, 'SELECT * FROM DBName.dbo.TableName')
SELECT * FROM LocalTable;

-- 更新本地表（遠端為來源）
UPDATE Local
SET Local.ColB = Remote.ColB
FROM OPENQUERY(LNK, 'SELECT * FROM DBName.dbo.TableName') Remote
INNER JOIN LocalTable Local ON Remote.KeyCol = Local.KeyCol;

4. OPENDATASOURCE（臨時、單次、連線字串內建）

-- 查詢
SELECT * FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=RemoteIP;User ID=User;Password=Pwd').DBName.dbo.TableName;

-- 匯入（同樣需先建表再 INSERT）
INSERT INTO OPENDATASOURCE('SQLOLEDB', 'Data Source=RemoteIP;User ID=User;Password=Pwd').DBName.dbo.TableName
SELECT * FROM LocalTable;

-- 更新遠端表（注意別名用法）
UPDATE Remote
SET Remote.ColA = Local.ColA
FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=RemoteIP;User ID=User;Password=Pwd').DBName.dbo.TableName Remote
INNER JOIN LocalTable Local ON Remote.KeyCol = Local.KeyCol;

關鍵限制與最佳實踐

無法 SELECT INTO 的替代方案

1. 遠端先建立同結構空表（可用 SELECT TOP 0 * INTO RemoteTable FROM LocalTable 配合 Linked Server）。
2. 本地用 SELECT * INTO #Temp FROM OPENROWSET(...) 先落地暫存表，再 INSERT INTO RemoteTable SELECT * FROM #Temp。
3. 使用 BCP OUT/IN 或 BULK INSERT 搭配檔案中繼，大量資料效能最佳。

安全性建議

• 避免在程式碼硬編碼密碼，改用 sp_addlinkedsrvlogin 對應 Windows 驗證或儲存憑證。
• 啟用 Ad Hoc Distributed Queries 伺服器選項（sp_configure 'show advanced options',1; RECONFIGURE; sp_configure 'Ad Hoc Distributed Queries',1; RECONFIGURE;）才能使用 OPENROWSET/OPENDATASOURCE。
• 生產環境建議統一使用 Linked Server + Windows Authentication，集中管理與稽核。

掌握四大語法差異，即可因地制宜選擇最適合的跨伺服器資料操作方式。