CODEWITHSUNDEEP
sqlsql-server
imba22
imba22

Reputation: 649

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

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)

Upvotes: 4

Views: 10421

Answers (3)

SQL Police
SQL Police

Reputation: 4196

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;

Upvotes: 0

Matt Gibson
Matt Gibson

Reputation: 38238

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.

Upvotes: 1

Zi0n1
Zi0n1

Reputation: 594

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.

Upvotes: 1

Related Questions