Slowly changing dimensions using T-SQL MERGE

• DamianC

  SSCrazy Eights

  Points: 8023

  More actions

  November 28, 2016 at 6:59 am

  #1914711

  Use these techniques for merges a lot

  Never used SCD 3 though and never used deleted

  Very useful to have them all in one location

  Thanks

  - Damian

• DamianC

  SSCrazy Eights

  Points: 8023

  More actions

  November 28, 2016 at 9:10 am

  #1914762

  Just playing around with SCD4 as I've used a different approach previously

  I'm getting:

  (5 row(s) affected)

  (0 row(s) affected)

  Msg 515, Level 16, State 2, Line 60

  Cannot insert the value NULL into column 'BusinessKey', table 'SQLServerCentral.dbo.Client_SCD4_History'; column does not allow nulls. INSERT fails.

  The statement has been terminated.

  So, I've dropped in a selection from @Client_SCD4 and run up to this point:

  OUTPUT DELETED.BusinessKey, DELETED.ClientName, DELETED.Country, DELETED.Town, DELETED.Address1, DELETED.Address2, DELETED.ClientType, DELETED.ClientSize, $Action AS MergeAction

  INTO @Client_SCD4 (BusinessKey, ClientName, Country, Town, Address1, Address2, ClientType, ClientSize, MergeAction)

  ;

  select * from @Client_SCD4

  This gives 5 rows of NULLS with this exception of merge action which is set to INSERT

  I would have expected this to be blank as I'm not actually deleting anything on the first run

  Am I correct?

  - Damian

• Eirikur Eiriksson

  SSC Guru

  Points: 182901

  More actions

  November 28, 2016 at 9:18 am

  #1914766

  DamianC (11/28/2016)

  ---

  Use these techniques for merges a lot

  Never used SCD 3 though and never used deleted

  Very useful to have them all in one location

  Thanks

  What are the business / reporting requirements?

  😎

• DamianC

  SSCrazy Eights

  Points: 8023

  More actions

  November 28, 2016 at 9:56 am

  #1914778

  In this instance I am using the sample data provided

  - Damian