SQL 2005 SCRIPT to COMPARE

• MC-231403

  Old Hand

  Points: 361

  More actions

  October 6, 2008 at 10:02 am

  #72895

  HI

  Please help, I need to write a script that will check two tables by a candidateID to see if any candidate are missing that are in Table1 that are not in table2 and then check the other way round.

  The (candidateID are unique) and may not be in the same row number. I need to identify the CandidateID so a count would not work.

  This is so I can import the missing candidates into both tables so they match.

  SQL 2005 standard clustered 9.0.3175

  I have been using distinct but it not give the right data.

  Thank for your help!

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  October 6, 2008 at 10:36 am

  #881224

  Something like this should work:

  SELECT T.CandidateID,

  Missing=CASE WHEN T1.CandidateID IS NULL THEN 'Missing From Table 1'

  WHEN T2.CandidateID IS NULL THEN 'Missing From Table 2'

  ELSE 'Present in Both'

  END

  FROM (SELECT DISTINCT CandidateID

  FROM TABLE1

  UNION ALL

  SELECT DISTINCT CandidateID

  FROM TABLE2) T

  LEFT JOIN Table1 T1 ON T.CandidateID = T1.CandidateID

  LEFT JOIN Table2 T2 ON T.CandidateID = T2.CandidateID

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup. 😉

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736270

  More actions

  October 6, 2008 at 11:27 am

  #881253

  I think the solution above should give you results. Left joins show nulls where something doesn't match.

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  October 6, 2008 at 4:17 pm

  #881378

  SELECT T1.CandidateID, 'Missing From Table 2'

  FROM Table1 T1

  WHERE NOT EXISTS (select 1 from Table2 T2 where T1.CandidateID = T2.CandidateID)

  UNION ALL

  SELECT T2.CandidateID, 'Missing From Table 1'

  FROM Table2 T2

  WHERE NOT EXISTS (select 1 from Table1 T1 where T1.CandidateID = T2.CandidateID)

  _____________
  Code for TallyGenerator

• MC-231403

  Old Hand

  Points: 361

  More actions

  October 8, 2008 at 1:37 am

  #882123

  Hi Would like to say thank you for the below code It worked great.

  SELECT T.CandidateID,

  Missing=CASE WHEN T1.CandidateID IS NULL THEN 'Missing From Table 1'

  WHEN T2.CandidateID IS NULL THEN 'Missing From Table 2'

  ELSE 'Present in Both'

  END

  FROM ( SELECT DISTINCT CandidateID

  FROM TABLE1

  UNION ALL

  SELECT DISTINCT CandidateID

  FROM TABLE2) T

  LEFT JOIN Table1 T1 ON T.CandidateID = T1.CandidateID

  LEFT JOIN Table2 T2 ON T.CandidateID = T2.CandidateID

  Thanks again.