none
How to perform SCD2 using MERGE Upsert

    Question

  • /* Merge Upsert Example SCD 2*/
    DECLARE @AuditKey INT; SET @AuditKey = ?; /* Need to pass in the current cycle id / audit key in our case here! */
    
    WITH ExistingDimension AS
    	(
    		SELECT CustomerKey, BKCustomerID, CustomerFirstName, CustomerLastName, CustomerAge, Hashcode, RowIsCurrent, RowChangeReason, RowStartDate, RowEndDate, InsertAuditKey, UpdateAuditKey FROM DimCustomer 
    		WHERE RowIsCurrent = 'Y' 
    		AND CustomerKey <> -1
    		AND HashCode NOT IN (SELECT HashCode FROM Conforming_DimCustomer WHERE RowChangeReason = 'UNCHANGED')
    	)
    MERGE ExistingDimension
    USING 
    	(
    		SELECT BKCustomerID, CustomerFirstName, CustomerLastName, CustomerAge, HashCode FROM Conforming_DimCustomer
    		WHERE RowChangeReason = 'CHANGED'
    	) AS ConformedDimension
    	
    
    
    /* *** Ensure that we are using the correct business keys here *** */	
    ON ExistingDimension.BKCustomerID = ConformedDimension.BKCustomerID	
    
    /* *** Perform UPDATE_SCD2 *** */
    WHEN MATCHED AND (ExistingDimension.CustomerFirstName <> ConformedDimension.CustomerFirstName) THEN UPDATE
    	SET [RowChangeReason] = 'EXPIRED_SCD2'
    	,[RowIsCurrent] = 'N'
    	,[RowEndDate] = GETDATE()
    	,UpdateAuditKey = @AuditKey;
    
    --WHEN MATCHED AND (ExistingDimension.CustomerFirstName <> ConformedDimension.CustomerFirstName) 
    --THEN INSERT
    --	(BKCustomerID, CustomerFirstName, CustomerLastName, CustomerAge, RowIsCurrent, RowChangeReason, RowStartDate, RowEndDate, InsertAuditKey, UpdateAuditKey, HashCode)
    --	VALUES
    --	(BKCustomerID, CustomerFirstName, CustomerLastName, CustomerAge, 'Y', 'INSERT_SCD2', GETDATE(), '9999/12/31', @AuditKey, @AuditKey, HashCode);
    	
    INSERT INTO DimCustomer 
    	(BKCustomerID, CustomerFirstName, CustomerLastName, CustomerAge, RowIsCurrent, RowChangeReason, RowStartDate, RowEndDate, InsertAuditKey, UpdateAuditKey, HashCode)
    	SELECT	Conformed.BKCustomerID, Conformed.CustomerFirstName, Conformed.CustomerLastName, Conformed.CustomerAge, 'Y', 'INSERT_SCD2', GETDATE(), '9999/12/31', @AuditKey, @AuditKey, Conformed.HashCode 
    		FROM Conforming_DimCustomer AS Conformed INNER JOIN DimCustomer 
    			ON Conformed.BKCustomerId = DimCustomer.BKCustomerId 
    			AND DimCustomer.UpdateAuditKey = @AuditKey
    			AND DimCustomer.RowIsCurrent = 'N'
    			AND DimCustomer.RowChangeReason = 'EXPIRED_SCD2';
    

    Hi Everyone,

    I have successfully used Merge Upsert to do normal SCD type 1 tracking (inserts, updates, expires)

    But now I am trying to do the same for SCD 2.

    This is what I am currently doing - but i think it is inefficient because after doing the EXPIRED_SCD2 checks using merge upsert I have to do a separate insert - by joining the source and target tables again: It works - but can it be done better for SCD2?

     

     

     


    I.W Coetzer
    Wednesday, August 24, 2011 8:48 AM

Answers

  • hi,

    you can use the output clause:

    quick example

    create table #t(id int)
    insert into #t
    select 1
    Go

    create table #t1(id int,name char(1))
    insert into #t1
    select 1,'a'
    GO
    create table #t2(id int,name char(1))
    GO

    merge #t1 as target using #t as source on target.id=source.id
    when matched then update set name='r'
    output inserted.* into #t2 ;
    GO

    select * from #t2
    GO

    drop table #t
    drop table #t1
    drop table #t2
    GO

     


    Thanks and regards, Rishabh , Microsoft Community Contributor
    • Marked as answer by I.W Coetzer Wednesday, August 24, 2011 2:32 PM
    Wednesday, August 24, 2011 9:03 AM

All replies

  • Basically when it is a match and one of the SCD2 columns (in this case first name) is different - it must update the current record in the dimension by expiring it, and then insert a new record as well - but i cannot seem to do an insert as well? it fails with this error:

     

    Msg 10711, Level 15, State 1, Line 30

     

    An action of type 'INSERT' is not allowed in the 'WHEN MATCHED' clause of a MERGE statement.


    I.W Coetzer
    Wednesday, August 24, 2011 8:53 AM
  • hi,

    you can use the output clause:

    quick example

    create table #t(id int)
    insert into #t
    select 1
    Go

    create table #t1(id int,name char(1))
    insert into #t1
    select 1,'a'
    GO
    create table #t2(id int,name char(1))
    GO

    merge #t1 as target using #t as source on target.id=source.id
    when matched then update set name='r'
    output inserted.* into #t2 ;
    GO

    select * from #t2
    GO

    drop table #t
    drop table #t1
    drop table #t2
    GO

     


    Thanks and regards, Rishabh , Microsoft Community Contributor
    • Marked as answer by I.W Coetzer Wednesday, August 24, 2011 2:32 PM
    Wednesday, August 24, 2011 9:03 AM
  • Hi

    Thank you for the reply - the output inserted .... got me in the right direction - I am now doing this:

     

    /* Merge Upsert Example SCD 2*/
    DECLARE @AuditKey INT; SET @AuditKey = ?; /* Need to pass in the current cycle id / audit key in our case here! */
    
    WITH ExistingDimension AS
    	(
    		SELECT CustomerKey, BKCustomerID, CustomerFirstName, CustomerLastName, CustomerAge, Hashcode, RowIsCurrent, RowChangeReason, RowStartDate, RowEndDate, InsertAuditKey, UpdateAuditKey FROM DimCustomer 
    		WHERE RowIsCurrent = 'Y' 
    		AND CustomerKey <> -1
    		AND HashCode NOT IN (SELECT HashCode FROM Conforming_DimCustomer WHERE RowChangeReason = 'UNCHANGED')
    	)
    MERGE ExistingDimension
    USING 
    	(
    		SELECT BKCustomerID, CustomerFirstName, CustomerLastName, CustomerAge, HashCode FROM Conforming_DimCustomer
    		WHERE RowChangeReason = 'CHANGED'
    	) AS ConformedDimension
    	
    /* *** Ensure that we are using the correct business keys here *** */	
    ON ExistingDimension.BKCustomerID = ConformedDimension.BKCustomerID	
    
    /* *** Perform UPDATE_SCD2 *** */
    WHEN MATCHED AND (ExistingDimension.CustomerFirstName <> ConformedDimension.CustomerFirstName) THEN UPDATE
    	SET [RowChangeReason] = 'EXPIRED_SCD2'
    	,[RowIsCurrent] = 'N'
    	,[RowEndDate] = GETDATE()
    	,UpdateAuditKey = @AuditKey
    	OUTPUT 
    		INSERTED.BKCustomerID, 	
    		ConformedDimension.CustomerFirstName, 
    		ConformedDimension.CustomerLastName, 
    		ConformedDimension.CustomerAge, 
    		'Y' AS RowIsCurrent, 
    		'INSERT_SCD2' AS RowChangeReason, 
    		GETDATE() AS RowStartDate, 
    		'9999/12/31' AS RowEndDate, 
    		@AuditKey AS InsertAuditKey, 
    		@AuditKey AS UpdatedAuditKey, 
    		ConformedDimension.HashCode 
    	INTO DimCustomer;
    


    I.W Coetzer
    Wednesday, August 24, 2011 2:28 PM