Querying data by joining two tables in two databases on different servers

sql

linked-servers

openquery

temporary-tables

byAnton Shumikhin·Oct 21, 2024

You can combine tables on separate servers with linked servers or OPENQUERY. Here's how you set up a linked server and use it in your query:

-- On your marks, get set... 
EXEC sp_addlinkedserver @server='RemoteServer'; -- Start your engines!

-- Race to join tables
SELECT l.Name, r.SalesAmount
FROM localDB.dbo.Customers l
JOIN RemoteServer.remoteDB.dbo.Sales r
  ON l.CustomerID = r.CustomerID; -- Who said SQL wasn't exciting?

Make sure the server names, database names, and table names align with your setup. Use linked servers for recurring joins or OPENQUERY for one-time, pass-through queries on the remote server.

Step-by-step procedure

Establishing linked servers

Kick things off by creating a linked server using sp_addlinkedserver. This allows you to treat the remote server like it's part of the family (or your local server, that is):

-- Knock, Knock... 
EXEC sp_addlinkedserver @server='RemoteServer'; -- Who's there? It's your new linked server!

When using linked servers, remember to cross the t's and dot the i's when it comes to secure access rights. A DBA might be your new best friend here.

Boosting query performance with OPENQUERY

Elevate your query performance with OPENQUERY. It can filter data on the remote server before it's transferred over, like an overzealous customs officer:

-- Remote server data, prepare to be filtered!
SELECT *
FROM OPENQUERY(RemoteServer, 'SELECT SalesID, SalesAmount FROM remoteDB.dbo.Sales WHERE SalesDate > ''20230101''') AS r
JOIN localDB.dbo.Customers l
  ON l.CustomerID = r.SalesID; -- Look ma, no unnecessary data transfers!

This technique minimizes data traffic, reducing rush hour for your network.

Temporary tables for added juice

For complex joins that repeat more than a favorite song, consider using temporary tables:

-- Let's cook up a temp table
SELECT *
INTO #LocalSalesData
FROM OPENQUERY(RemoteServer, 'SELECT SalesID, SalesAmount FROM remoteDB.dbo.Sales');

-- Now for the main course
SELECT l.Name, s.SalesAmount
FROM localDB.dbo.Customers l
JOIN #LocalSalesData s
  ON l.CustomerID = s.SalesID; -- Bon Appétit!

Strategies for the big leagues

If you're dealing with a heavy workload wrangling large datasets, consider ETL processes or mirroring table copies—especially for delta updates. Always consider data volume and network traffic—think of your network as a highway during rush hour.

Pro tips for better query experience

Make a loud, clear statement with fully qualified names—eliminate any room for ambiguity.
Never break old habits—review SQL syntax frequently to ensure compatibility.
Opt for OPENROWSET for those ad-hoc queries that don't warrant a linked server setup.
Anticipate and strategize for security measures right from setting up remote database connections.
Join a subset of remote tables to create a focused and efficient query processing engine.