How to compare data between two table in different databases using Sql Server 2008?

Solution 1:

select * 
from (
      select 'T1' T, *
      from DB1.dbo.Table
      except
      select 'T2' T, *
      from DB2.dbo.Table
     ) as T
union all
select * 
from (
      select 'T2' T, *
      from DB2.dbo.Table
      except
      select 'T1' T, *
      from DB1.dbo.Table
     ) as T
ORDER BY 2,3,4, ..., 1  -- make T1 and T2 to be close in output 2,3,4 are UNIQUE KEY SEGMENTS

Test code:

declare @T1 table (ID int)
declare @T2 table (ID int)

insert into @T1 values(1),(2)
insert into @T2 values(2),(3)

select * 
from (
      select *
      from @T1
      except
      select *
      from @T2
     ) as T
union all
select * 
from (
      select *
      from @T2
      except
      select *
      from @T1
     ) as T

Result:

ID
-----------
1
3

Note: It can take long time to compare big table, when developing "tuned" solution or refactorig, which will give same result as REFERERCE - it may be wise to chekc simple parameters first: like

select count(t.*) from (
   select count(*) c0, SUM(BINARY_CHECKSUM(*)%1000000) c1 FROM T_REF_TABLE 
   -- select 12345 c0, -214365454 c1 -- constant values FROM T_REF_TABLE 
   except 
   select count(*) , SUM(BINARY_CHECKSUM(*)%1000000) FROM T_WORK_COPY 
) t

When this is empty, you have probably things under controll, and may be you can modify when you fail you will see "constant values FROM T_REF" to isert to save even more time for next check!!!

Solution 2:

I’d really suggest that people who encounter this problem go and find a third party database comparison tool.

Reason – these tools save a lot of time and make the process less error prone.

I’ve used comparison tools from ApexSQL (Diff and Data Diff) but you can’t go wrong with other tools marc_s and Marina Nastenko already pointed out.

If you’re absolutely sure that you are only going to compare tables once then SQL is fine but if you’re going to need this from time to time you’ll be better off with some 3rd party tool.

If you don’t have budget to buy it then just use it in trial mode to get the job done.

I hope new readers will find this useful even though it’s a late answer…

Solution 3:

I'v done things like this using the Checksum(*) function

In essance it creates a row level checksum on all the columns data, you could then compare the checksum of each row for each table to each other, use a left join, to find rows that are different.

Hope that made sense...

Better with an example....

select *
from 
( select checksum(*) as chk, userid as k from UserAccounts) as t1
left join 
( select checksum(*) as chk, userid as k from UserAccounts) as t2 on t1.k = t2.k
where t1.chk <> t2.chk