SQL Server 跨資料庫、跨伺服器查詢完整指南：四部份命名、連結伺服器、OPENROWSET

同一實例跨資料庫查詢

-- 四部份命名：Server.Database.Schema.Object (同實例 Server 省略)
SELECT * FROM DatabaseA.dbo.TableA a
JOIN DatabaseB.dbo.TableB b ON a.id = b.id;

-- 簡寫 (dbo 可省略)
SELECT * FROM DatabaseA..TableA a, DatabaseB..TableB b WHERE a.id = b.id;

跨伺服器：連結伺服器

-- 1. 建立連結伺服器
EXEC sp_addlinkedserver @server='RemoteSVR', @srvproduct=N'SQL Server';
EXEC sp_addlinkedsrvlogin @rmtsrvname='RemoteSVR', @useself='false', @locallogin=NULL, @rmtuser='sa', @rmtpassword='pwd';

-- 2. 四部份命名查詢
SELECT * FROM RemoteSVR.DatabaseName.dbo.TableName;

-- 3. 導入本地
SELECT * INTO LocalTable FROM RemoteSVR.DatabaseName.dbo.TableName;

-- 4. 寫入遠端
INSERT RemoteSVR.DatabaseName.dbo.TableName SELECT * FROM LocalTable;

-- 5. 更新本地 (Join 遠端)
UPDATE b SET b.ColA = a.ColA
FROM RemoteSVR.DatabaseName.dbo.TableName a
JOIN LocalTable b ON a.Key = b.Key;

-- 6. 不再使用時移除
EXEC sp_dropserver 'RemoteSVR', 'droplogins';

Ad Hoc 分散式查詢 (無需永久連結伺服器)

OPENROWSET

-- 查詢
SELECT * FROM OPENROWSET('SQLOLEDB', 'RemoteSVR'; 'sa'; 'pwd', 'DatabaseName.dbo.TableName');

-- 導入本地
SELECT * INTO LocalTable FROM OPENROWSET('SQLOLEDB', 'RemoteSVR'; 'sa'; 'pwd', 'DatabaseName.dbo.TableName');

-- 寫入遠端
INSERT OPENROWSET('SQLOLEDB', 'RemoteSVR'; 'sa'; 'pwd', 'DatabaseName.dbo.TableName')
SELECT * FROM LocalTable;

-- 更新本地
UPDATE b SET b.ColA = a.ColA
FROM OPENROWSET('SQLOLEDB', 'RemoteSVR'; 'sa'; 'pwd', 'DatabaseName.dbo.TableName') a
JOIN LocalTable b ON a.Key = b.Key;

OPENQUERY (需連結伺服器)

SELECT * FROM OPENQUERY(RemoteSVR, 'SELECT * FROM DatabaseName.dbo.TableName');
INSERT OPENQUERY(RemoteSVR, 'SELECT * FROM DatabaseName.dbo.TableName') SELECT * FROM LocalTable;

OPENDATASOURCE (一次性連線字串)

SELECT * FROM OPENDATASOURCE('SQLOLEDB', 'Data Source=RemoteSVR;User ID=sa;Password=pwd').DatabaseName.dbo.TableName;
INSERT OPENDATASOURCE('SQLOLEDB', 'Data Source=RemoteSVR;User ID=sa;Password=pwd').DatabaseName.dbo.TableName
SELECT * FROM LocalTable;

啟用 Ad Hoc 分散式查詢

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;

效能與安全建議

• 優先使用 **連結伺服器** (連線池、計畫重用、安全性集中管理)。
• OPENROWSET/OPENDATASOURCE 適合臨時/動態伺服器，但每次建立連線、無計畫重用。
• 大量資料跨伺服器搬移：考慮 **SSIS、BCP、複寫、PolyBase** 等專用工具。
• 跨伺服器 JOIN 易產生效能問題：盡可能先篩選遠端資料再傳輸，或將遠端結果暫存本地再 JOIN。
• 防火牆、Kerberos 雙跳驗證、權限最小化 (連結伺服器登入對應)。

其他資料庫 (Oracle, MySQL, PostgreSQL)

• 安裝對應 OLE DB / ODBC Provider。
• sp_addlinkedserver @provider='OraOLEDB.Oracle', @datasrc='TNS名' 等。
• 查詢語法相同，四部份命名中 Catalog 對應 Schema/Database。