1

I have a STG table where we truncate and load values into the STG table from 2 SRC tables daily. I created a Change flag (Ch_Flg) and in the STG table so that instead of truncating the tables daily, we will insert values into STG only when certain fields in SRC have changed.

However, since SRC table loads directly into STG, I need a temp table in between where I can store all the SRC records and check with STG if the records have changed and insert into STG only when those records have changed.

For example the SRC1 table has columns like (ID, Name, Tag, Sector, Dept, City, Phone, HireDate etc) and SRC2 has columns like (ID, Name, Competency, City, Phone etc)

Should I load all the values from SRC into a temp table and then compare them against the STG table? Or is there a more efficient way to do this?

curious_learner
  • 43
  • 1
  • 9

1 Answers1

0

You could load them into the temp table and then do something like this

WITH SINGLES AS 
(
 SELECT t1.ID
 FROM <sourcetablename> t1
 LEFT JOIN <desttablename> t2
 ON (t1.id = t2.id)
 WHERE t2.id IS NULL
 )

 INSERT <tabletherecordswillbeinserted>(ID, <otherfield>, <otherfield2>)

 SELECT S.id, S.<otherfield1>, s.<otherfield2>
 From singles S
 Left Join <sourcetablename> on S.id = <desttable>.id
 Where <desttable>.id Is Null

You may need to add in the change flag to the code but that's the general idea, you could put the change flag in the CTE statement

DrHouseofSQL
  • 550
  • 5
  • 16
  • Thanks for the structure. what is with singles as? – curious_learner Jul 14 '16 at 17:50
  • That is called a 'Common Table Expression' or CTE, it is a way to gather up a specific set of data and do something else with it. So in this case if you take the select statement inside of the With and run it with your tables, you will get back everything from one table and not in another. Then we take that set of data and insert the records that are nonexistent into the destination table. In your case I imagine you are only interested in records with a populated change flag. So you could do your with to get the records changed and then insert them below. – DrHouseofSQL Jul 14 '16 at 18:04
  • edit: All of the 'other fields' I have in the bottom of the script would need to be in your original select in the WITH statement. Sorry about that – DrHouseofSQL Jul 14 '16 at 18:16
  • so 'with singles as' is a CTE and not something that is part of the code? I get an error when I am writing ' With Singles As' – curious_learner Jul 14 '16 at 20:55
  • It is part of the code (note that the word 'singles' appears in the 2nd select script), you could name it anything you wanted though as long as you change the name in the 2nd select statement. You will get an error when running it unless you are running all of the code. Try running the first select you have (just the select) without the 'With Singles as' and see if the select works. Then add on from there, hope this helps. – DrHouseofSQL Jul 14 '16 at 21:02