SQL Compare Without The Price Tag
From Wiki
You want to know the differences between 2 tables but don’t have the money to pay for SQL Compare by red-gate software and your trial version has expired ;-(
Let me say first of all that SQL Compare is probably the best third party product around for SQL Server (along with SQL LITESPEED by QUEST Software)
In order to see what rows are in table 1 and not in table 2 and vice versa you can do 2 left joins, 2 right joins or 1 left and 1 right join. To get the rows that are different you can use CHECKSUM
Let’s get started…
- --let's copy over 20 rows to a table named authors2
- SELECT TOP 20 * INTO tempdb..authors2
- FROM pubs..authors
- --update 5 records by appending X to the au_fname
- SET ROWCOUNT 5
- UPDATE tempdb..authors2
- SET au_fname =au_fname +'X'
- --Set rowcount back to 0
- SET ROWCOUNT 0
- --let's insert a row that doesn't exist in pubs
- INSERT INTO tempdb..authors2
- SELECT '666-66-6666', au_lname, au_fname, phone, address, city, STATE, zip, contract
- FROM tempdb..authors2
- WHERE au_id ='172-32-1176'
- --*** The BIG SELECT QUERY --***
- --Not in Pubs
- SELECT 'Does Not Exist On Production',t2.au_id
- FROM pubs..authors t1
- RIGHT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
- WHERE t1.au_id IS NULL
- UNION ALL
- --Not in Temp
- SELECT 'Does Not Exist In Staging',t1.au_id
- FROM pubs..authors t1
- LEFT JOIN tempdb..authors2 t2 ON t1.au_id =t2.au_id
- WHERE t2.au_id IS NULL
- UNION ALL
- --Data Mismatch
- SELECT 'Data Mismatch', t1.au_id
- FROM( SELECT BINARY_CHECKSUM(*) AS CheckSum1 ,au_id FROM pubs..authors) t1
- JOIN(SELECT BINARY_CHECKSUM(*) AS CheckSum2,au_id FROM tempdb..authors2) t2 ON t1.au_id =t2.au_id
- WHERE CheckSum1 <> CheckSum2
- --Clean up
- DROP TABLE tempdb..authors2
- GO
You can run this whole script in 1 shot
Let me finish by saying that this code should just be used for quick checking. SQL Compare is far superior to this and will also generate the scripts for you to synchronize the 2 tables. So if you only need to do something like this once then use this code or download the SQL compare trial version. If you do this on a regular basis then you should seriously consider getting SQL compare
I am not being paid or endorsed in any way by red-gate, I just happen to like their product a lot and it saved me a lot of time and trouble once a developer went on vacation
This query below was submitted to me by Louis Davidson, basically a FULL OUTER JOIN instead of a UNION
- SELECT CASE WHEN t1.au_id IS NULL
- AND t2.au_id IS NOT NULL
- THEN 'Does Not Exist On Production'
- WHEN t1.au_id IS NOT NULL
- AND t2.au_id IS NULL
- THEN 'Does Not Exist In Staging'
- ELSE 'Data Mismatch' END,
- COALESCE(t1.au_id, t2.au_id) AS au_id
- FROM (SELECT *, BINARY_CHECKSUM(*) AS bc FROM pubs..authors) AS t1
- FULL OUTER JOIN (SELECT *, BINARY_CHECKSUM(*) AS bc FROM tempdb..authors2) AS t2
- ON t1.au_id =t2.au_id
- WHERE t1.au_id IS NULL
- OR t2.au_id IS NULL
- OR t1.bc <> t2.bc
Contributed by: --SQLDenis 14:11, 14 June 2008 (GMT)
Part of SQL Server Admin Hacks
