T-SQL (SCD) Slowly Changing Dimension Type 2 using a merge statement
- by AtulThakor
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:
