How to fetch data from two different sql servers?

Question

Hi,

I have an inline query, in which I have one table1 in server1 and another table2 in server2. I need to join these two tables, and fetch data.

I can do this like connect to one server, get data and connect to next server...fetch data. and join them.

But is there any other better way. I have heard about Linked servers. Will that help here ?

Thanks in advance !!!

Answer 1

Yes, set up a linked server on one server to the other. Then you can just do a normal query with a join. It would look something like this:

SELECT t1.Col1
    ,  t2.ColA
FROM server1Table t1
INNER JOIN SERVER2.dbname.dbo.tableName t2 ON t1.TheId = t2.TheId

this assumes you're running the query on Server1. You can also have two linked servers and reference them both using [servername].[dbname].[schema].[table] and then use in SQL as normal.

Alternatively, you can use OPENROWSET (but linked server is easiest if you're able to set that up). OpenRowSets look like this:

SELECT t1.Col1
    ,  t2.ColA
FROM server1Table t1
INNER JOIN  OPENROWSET('SQLNCLI', 'Server=Server2;Trusted_Connection=yes;',
                       'SELECT t2.ColA, t2.TheId FROM dbname.dbo.tableName') AS t2
  ON t1.TheId = t2.TheId

and then you can just join on 'a' as if it's a local table. Under the hood it's probably pulling all the data down to your local database, so you should consider adding WHERE to the inner query to restrict rows, and only get the columns you need.