Comparing large tables using checksum

• SQL Guy 1

  SSCoach

  Points: 15981

  More actions

  February 24, 2012 at 2:50 pm

  #257476

  I have two large tables, in terms of number of records as well as number of columns. Recordcount is equal.

  I need to find differences in record values accross all columns. Tried to use checksum(*), but the syntax did not work, also tried to use it as computed column, and tried HashBytes as well.

  Does somebody has an example ? If that's impossible, what would be a better method ?

  Thanks

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  February 24, 2012 at 7:38 pm

  #1451669

  Have you considered using the MERGE statement? Greatly modified to NOT change anything simple to find those rows.

  MERGE dbo.Departments AS d

  USING dbo.Departments_delta AS dd

  ON (d.DeptID = dd.DeptID)

  WHEN NOT MATCHED THEN -- modify to insert records into a temp table

  INSERT (DeptID, DeptName, Manager)

  VALUES (dd.DeptID, dd.DeptName, dd.Manager)

  WHEN NOT MATCHED BY SOURCE THEN

  DELETE -- modify to insert records into a temp table

  OUTPUT $action,

  inserted.DeptID AS SourceDeptID, inserted.DeptName AS SourceDeptName,

  inserted.Manager AS SourceManager,

  deleted.DeptID AS TargetDeptID, deleted.DeptName AS TargetDeptName,

  deleted.Manager AS TargetManager;

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]