Getting data from different database on different server with one SQL Server query

Question

Server1: Prod, hosting DB1
Server2: Dev hosting DB2

Is there a way to query databases living on 2 different server with a same select query? I need to bring all the new rows from Prod to dev, using a query like below. I will be using SQL Server DTS (import export data utility)to do this thing.

Insert into Dev.db1.table1
    Select * 
    from Prod.db1.table1
    where table1.PK not in (Select table1.PK from Dev.db1.table1)

How to create a linked server https://msdn.microsoft.com/en-us/library/ff772782.aspx — Horaciux, Oct 20 '15 at 20:25
@kushalbhola - which solution worked for you ? Could you approve that one ? — Saurabh, Aug 16 '17 at 15:00

score 1 · Accepted Answer · answered Oct 20 '15 at 20:41

Creating a linked server is the only approach that I am aware of for this to occur. If you are simply trying to add all new rows from prod to dev then why not just create a backup of that one particular table and pull it into the dev environment then write the query from the same server and database?

Granted this is a one time use and a pain for re-occuring instances but if it is a one time thing then I would recommend doing that. Otherwise make a linked server between the two.

To backup a single table in SQL use the SQl Server import and export wizard. Select the prod database as your datasource and then select only the prod table as your source table and make a new table in the dev environment for your destination table.

This should get you what you are looking for.

If your servers are all in an Active Directory, and when you use Windows Authentification, then you don't need linked servers. — SQL Police, Oct 20 '15 at 21:55

score 1 · Answer 2 · edited May 23 '17 at 12:29

You say you're using DTS; the modern equivalent would be SSIS.

Typically you'd use a data flow task in an SSIS package to pull all the information from the live system into a staging table on the target, then load it from there. This is a pretty standard operation when data warehousing.

There are plenty of different approaches to save you copying all the data across (e.g. use a timestamp, use rowversion, use Change Data Capture, make use of the fact your primary key only ever gets bigger, etc. etc.) Or you could just do what you want with a lookup flow directly in SSIS...

The best approach will depend on many things: how much data you've got, what data transfer speed you have between the servers, your key types, etc.

score 0 · Answer 3 · answered Oct 20 '15 at 21:54

When your servers are all in one Active Directory, and when you use Windows Authentification, then all you need is an account which has proper rights on all the databases!

You can then simply reference all tables like server.database.schema.table

For example:

insert into server1.db1.dbo.tblData1 (...) 
select ... from server2.db2.dbo.tblData2;

Getting data from different database on different server with one SQL Server query

3 Answers3