SQL Server 2005 script with join across Database Servers

Question

I have the following script which I use to give me a simple "diff" between tables on two different databases. (Note: In reality my comparison is on a lot more than just an ID)

SELECT
    MyTableA.MyId,
    MyTableB.MyId
FROM
    MyDataBaseA..MyTable MyTableA
FULL OUTER JOIN
    MyDataBaseB..MyTable MyTableB
ON
    MyTableA.MyId = MyTableB.MyId
WHERE
    MyTableA.MyId IS NULL
OR
    MyTableB.MyId IS NULL

I now need to run this script on two databases that exist on different servers. At the moment my solution is to backup the database from one server, restore it to the other and then run the script.

I'm pretty sure this is possible, however, is this likely to be a can of worms? This is a very rare task I need to perform and if it involves a large number of DB setting changes then I will probably stick to my backup method.

Answer 1

you can do it with the help of openQuery function of in sql server

Example

Select QUERY.* from openquery(LinkedServerName, ' select tableA.row2 as R1,tableB.row1 as R2 from schema.tableA inner join schema.tableB on tableA.row1=tableB.row1') as QUERY
where QUERY.R1 = @var1
and QUERY.R2 = @var2

more detail about openQuery refer link : http://msdn.microsoft.com/en-us/library/ms188427.aspx

Answer 2

If you set up a Linked Server in SQL you can just run a regular query like so. This is assuming that MyDatabaseB is on the remote server that you set up the linked server to and the query is being run on the server that has MyDatabaseA.

SELECT
MyTableA.MyId,
MyTableB.MyId
FROM
MyDataBaseA..MyTable MyTableA
FULL OUTER JOIN
LinkedServerName.MyDataBaseB.dbo.MyTable MyTableB
ON
MyTableA.MyId = MyTableB.MyId
WHERE
MyTableA.MyId IS NULL
OR
MyTableB.MyId IS NULL

Answer 3

I too would recommend SSIS instead. Have two data sources one that select ID from MyTableA order by ID and one that select ID from MyTableB order by ID. The ORDER BY is important, and you'll need to go into the advanced editor and mark the output as sorted by ID in both sources. Then plug these two sources into a Merge Join transformation and specify that is a Full join type. Then you plug the output of the join into a Conditional Split Transformation and separate the rows with non-NULL A and B IDs from the ones that have NULL on either A's ID or B's ID. These last ones will be your 'diff'.

In my experience, as the table size will grow larger, the SSIS solution will become more and more appealing as the plan generated for the distributed query will become unmanageably inefficient.