Table compare

• djj

  SSCoach

  Points: 18831

  More actions

  March 26, 2014 at 6:57 am

  #291535

  Hello, I have two tables I am trying to compare as I have created a new procedure to replace an old one and want to check if the new procedure produces similar results.

  The problem is that when I run my compare I get false matches. Example:

  CREATE TABLE #ABC (Acct VARCHAR(10), Que INT);

  INSERT INTO #ABC VALUES

  ('2310947',110),

  ('2310947',245);

  CREATE TABLE #DEF (Acct VARCHAR(10), Que INT);

  INSERT INTO #DEF VALUES

  ('2310947',110),

  ('2310947',245);

  SELECT #ABC.*

  FROM #ABC

  INNER JOIN #DEF ON #ABC.Acct = #DEF.Acct

  WHERE #ABC.Que <> #DEF.Que;

  DROP TABLE #ABC;

  DROP TABLE #DEF;

  Which gives me two records when I really do not want any as the tables are identical.

  Any suggestions?

  Thank you

• mcx5000

  Old Hand

  Points: 365

  More actions

  March 26, 2014 at 7:43 am

  #1700286

  You need to join your example tables on both columns.

  You might also look at using EXCEPT. Something like

  select acct, que

  from #ABC

  except

  select acct, que

  from #DEF

• djj

  SSCoach

  Points: 18831

  More actions

  March 26, 2014 at 7:52 am

  #1700288

  Thank you, mcx5000.

  The EXECPT might just be what I was looking for. I have not used it very often so do not think of it.

  Thanks again,