How to combine two codes

• hoseam

  SSCertifiable

  Points: 5533

  More actions

  July 8, 2014 at 3:06 am

  #1727683

  They are two different Code but share those similarities. I thought this code was clear because it doesn't have much Columns.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  July 8, 2014 at 3:17 am

  #1727684

  SELECT

  a, -- column is in #TableA only

  b, -- column is in #TableA only

  c, -- column is in #TableA only

  d, -- column is in both tables

  e = CAST(e AS VARCHAR(10)), -- column is in both tables, INT in #TableA and VARCHAR(10) in #TableB *

  f, -- column is in both tables

  g = CAST(NULL AS INT), -- placeholder: column is in #TableB only

  h = CAST(NULL AS INT), -- placeholder: column is in #TableB only

  i = CAST(NULL AS INT) -- placeholder: column is in #TableB only

  FROM #TableA

  UNION ALL

  SELECT

  a = NULL, -- placeholder: column is in #TableA only

  b = NULL, -- placeholder: column is in #TableA only

  c = NULL, -- placeholder: column is in #TableA only

  d, -- column is in both tables

  e, -- column is in both tables, INT in #TableA and VARCHAR(10) in #TableB

  f, -- column is in both tables

  g, -- column is in #TableB only

  h, -- column is in #TableB only

  i -- column is in #TableB only

  FROM #TableB

  -- *INT will always cast to VARCHAR but VARCHAR won't always cast to INT.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden