SQL Server 2005 script with connection between database servers

Question

I have the following script that I use to give me a simple "diff" between tables in two different databases. (Note: actually my comparison is much 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

Now I need to run this script in two databases existing on different servers. At the moment, my solution is to backup the database from one server, restore it to another, and then run the script.

I am sure it is possible, however, perhaps it could be a worm made of worms? This is a very rare task that I need to perform, and if it involves a large number of changes to the database settings, I will probably stick to my backup method.

Answer 1

Linked Server SQL, . , MyDatabaseB , , 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 2

SSIS. : select ID from MyTableA order by ID select ID from MyTableB order by ID. ORDER BY , . , . -NULL A B ID , NULL ID B ID. "diff".

, , SSIS , , , .