Slowly changing dimensions using T-SQL MERGE

  • DamianC

    SSCertifiable

    Points: 7828

    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

    SSCertifiable

    Points: 7828

    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: 182438

    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

    SSCertifiable

    Points: 7828

    In this instance I am using the sample data provided

    - Damian

Viewing 4 posts - 31 through 34 (of 34 total)

You must be logged in to reply to this topic. Login to reply