Posted by Erland Sommarskog on 10/15/58 11:54

Brian Schultz (bdschultz@gmail.com) writes:
> I have two database that are duplicates of each other - but are on
> different servers.
>
> I need to write a script that will do a select from one table and then
> compare it to another select of that table - but on the db on the other
> server.
>
> Is it possible to do that in a script? If so, how?

SELECT ...
FROM localtbl l
FULL JOIN SERVER.db.dbo.remotetbl r ON l.keycol = r.keycol
WHERE l.keycol IS NULL
OR r.keycol IS NULL
OR a.col <> b.col
OR a.col IS NULL AND b.col IS NOT NULL
OR a.col IS NOT NULL AND b.col IS NULL

SERVER is here a linked server that you have set up with sp_addlinkedserver.

If you need to do this on a large-scale basis, you should probably
consider a third-party product. I believe Red Gate has something called
DataCompare.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]