in

SQL Server Blogs

Voices from the UK SQL Server Community

Atul Thakor

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:  

Comments

No Comments

About AtulThakor

Twitter:@AtulThakor
Powered by Community Server (Commercial Edition), by Telligent Systems