Compare Two Queries - Help

Hi All,

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?

Thanks in advance.Brian Schultz (bdschultz@.gmail.com) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||you could try firefly. it's a free tool that i wrote:
http://www.getfirefly.net/
let me know if you have any problems.
thanks,
James