Comparing Data

• netguykb

  Ten Centuries

  Points: 1310

  More actions

  July 31, 2009 at 2:26 pm

  #133537

  I have two tables with the same column name.

  There is a FK object creating a link between the two

  I.E.

  TABLE1

  ID|TotNum

  16725

  278229

  3123

  TABLE2

  ID|TotNum

  16725

  271230

  3456

  I want to loop through Table2 and compare TotNum with Table1 TotNum

  to ensure they are the same. If they are not then select(Print) those that are not from Table 2

  For example

  Record 2 in Table2, TotNum (71230) is less than Record 2 in Table1, TotNum (78229)

  Record 3 in Table2, TotNum (456) is less than Record 3 in Table1, TotNum (123)

  Eventually these results are run at the end of an SSIS package whith the results getting e-mailed if there are any discrepancies

• Ken Simmons

  SSCertifiable

  Points: 7822

  More actions

  July 31, 2009 at 2:33 pm

  #1033167

  You don't have to loop through the tables. You should be able to do something like this...

  DECLARE @TABLE1 TABLE(ID int, TotNum int)

  DECLARE @TABLE2 TABLE(ID int, TotNum int)

  INSERT @TABLE1

  SELECT 1, 6725 UNION ALL

  SELECT 2, 78229 UNION ALL

  SELECT 3, 123

  INSERT @TABLE2

  SELECT 1, 6725 UNION ALL

  SELECT 2, 71230 UNION ALL

  SELECT 3, 456

  SELECT A.ID, A.TotNum, B.TotNum

  FROM @TABLE1 A JOIN

  @TABLE2 B ON A.ID = B.ID AND A.TotNum B.TotNum