in

SQL Server Blogs

Voices from the UK SQL Server Community

Atul Thakor

January 2011 - Posts

  • T-SQL (SCD) Slowly Changing Dimension Type 2 using a merge statement

    Working on stored procedure recently which loads records into a data warehouse I found that the existing record was being expired using an update statement followed by an insert to add the new active record.

    Playing around with the merge statement you can actually expire the current record and insert a new record within one clean statement.

    This is how the statement works, we do the normal merge statement to insert a record when there is no match, if we match the record we update the existing record by expiring it and deactivating.

    At the end of the merge statement we use the output statement to output the staging values for the update,  we wrap the whole merge statement within an insert statement and add new rows for the records which we inserted. I’ve added the full script at the bottom so you can paste it and play around.

     

       1: INSERT INTO ExampleFactUpdate 
       2:     (PolicyID,
       3:     Status)
       4:    SELECT -- these columns are returned from the output statement
       5:     PolicyID,
       6:     Status
       7:    FROM 
       8:    (
       9:     -- merge statement on unique id in this case Policy_ID
      10:        MERGE dbo.ExampleFactUpdate dp
      11:        USING dbo.ExampleStag s
      12:            ON dp.PolicyID = s.PolicyID
      13:        WHEN NOT MATCHED THEN -- when we cant match the record we insert a new record record and this is all that happens
      14:            INSERT (PolicyID,Status)
      15:            VALUES (s.PolicyID, s.Status)
      16:        WHEN MATCHED --if it already exists
      17:            AND ExpiryDate IS NULL  -- and the Expiry Date is null
      18:             THEN
      19:            UPDATE
      20:                SET
      21:                    dp.ExpiryDate = getdate(), --we set the expiry on the existing record
      22:                    dp.Active = 0 -- and deactivate the existing record
      23:        OUTPUT $Action MergeAction, s.PolicyID, s.Status -- the output statement returns a merge action which can 
      24:    ) MergeOutput -- be insert/update/delete, on our example where a record has been updated (or expired in our case
      25:    WHERE -- we'll filter using a where clause
      26:        MergeAction = 'Update'; -- here
     
    Complete source for example
       1: if OBJECT_ID('ExampleFactUpdate') > 0
       2: drop table ExampleFactUpdate
       3:  
       4: Create Table ExampleFactUpdate(
       5:     ID int identity(1,1),   3: go
       6:     PolicyID varchar(100),
       7:     Status    varchar(100),
       8:     EffectiveDate datetime default getdate(),
       9:     ExpiryDate datetime,
      10:     Active bit default 1
      11: )
      12:  
      13:  
      14: insert into ExampleFactUpdate(
      15:     PolicyID,
      16:     Status)
      17: select
      18:     1,
      19:     'Live'
      20:  
      21: /*Create Staging Table*/
      22: if OBJECT_ID('ExampleStag') > 0
      23: drop table ExampleStag
      24: go
      25:  
      26: /*Create example fact table */
      27: Create Table ExampleStag(
      28:     PolicyID varchar(100),
      29:     Status    varchar(100))
      30:  
      31: --add some data
      32: insert into ExampleStag(
      33:     PolicyID,
      34:     Status)
      35: select
      36:     1,
      37:     'Lapsed'
      38: union all
      39: select
      40:     2,
      41:     'Quote'
      42:  
      43: select    *
      44: from    ExampleFactUpdate
      45:  
      46: select    *
      47: from    ExampleStag
      48:  
      49:  
      50: INSERT INTO ExampleFactUpdate 
      51:     (PolicyID,
      52:     Status)
      53:    SELECT -- these columns are returned from the output statement
      54:     PolicyID,
      55:     Status
      56:    FROM 
      57:    (
      58:     -- merge statement on unique id in this case Policy_ID
      59:        MERGE dbo.ExampleFactUpdate dp
      60:        USING dbo.ExampleStag s
      61:            ON dp.PolicyID = s.PolicyID
      62:        WHEN NOT MATCHED THEN -- when we cant match the record we insert a new record record and this is all that happens
      63:            INSERT (PolicyID,Status)
      64:            VALUES (s.PolicyID, s.Status)
      65:        WHEN MATCHED --if it already exists
      66:            AND ExpiryDate IS NULL  -- and the Expiry Date is null
      67:             THEN
      68:            UPDATE
      69:                SET
      70:                    dp.ExpiryDate = getdate(), --we set the expiry on the existing record
      71:                    dp.Active = 0 -- and deactivate the existing record
      72:        OUTPUT $Action MergeAction, s.PolicyID, s.Status -- the output statement returns a merge action which can 
      73:    ) MergeOutput -- be insert/update/delete, on our example where a record has been updated (or expired in our case
      74:    WHERE -- we'll filter using a where clause
      75:        MergeAction = 'Update'; -- here
      76:  
      77:  
      78: select    *
      79: from    ExampleFactUpdate
      80:  
Powered by Community Server (Commercial Edition), by Telligent Systems