I
feel happy that people around the globe reach my blog when they google for " SQL Server
Upsert ". You can read the Basic Merge Post here. It was for beginners who want to use Merge
for the first time and also for referral purpose. In that post, I only
discussed about changed overwrites (Slowly Changing Dimension (SCD) Type 1), and now in this post, we shall see how to use Merge for SCD Type 2 (Dates and Flag) implementation.
Source Table: DEPT
CREATE TABLE [dbo].[DEPT]
(
[DEPTNO] [numeric](2, 0) NULL,
[DNAME] [varchar](14) NULL,
[LOC] [varchar](13) NULL
)
Destination Table: DEPT_SCD2DATE
CREATE TABLE [dbo].[DEPT_SCD2DATE]
(
[ID] [int] IDENTITY(1,1) NOT NULL,
[DEPTNO] [numeric](2, 0) NULL,
[DNAME] [varchar](14) NULL,
[LOC] [varchar](13) NULL,
[STARTDATE] [datetime] NULL,
[ENDDATE] [datetime] NULL,
[FLAG] [bit] NULL
)
Merge Code:
INSERT INTO
DEPT_SCD2DATE (DEPTNO, DNAME, LOC, STARTDATE, ENDDATE, FLAG)
SELECT
DEPTNO, DNAME, LOC, STARTDATE, ENDDATE, FLAG FROM
(MERGE
DEPT_SCD2DATE D
USING DEPT S
ON (D.DEPTNO = S.DEPTNO)
WHEN NOT MATCHED
THEN
INSERT VALUES (S.DEPTNO, S.DNAME, S.LOC, GETDATE(), '2999-12-31', 1)
WHEN MATCHED AND D.FLAG = 1 AND (D.DNAME <> S.DNAME OR D.LOC <> S.LOC)
THEN
UPDATE SET D.FLAG = 0, D.ENDDATE = GETDATE()
OUTPUT $ACTION ACTIONOUT, S.DEPTNO, S.DNAME, S.LOC,
GETDATE() AS STARTDATE, '2999-12-31' AS
ENDDATE, 1 AS FLAG
) AS
MERGE_OUT
WHERE MERGE_OUT.ACTIONOUT = 'UPDATE';
Observation - Before Run 1:
Observation - After Run 1:
Observation - Before Run 2:
Observation - After Run 2:
Conclusion: Slowly Changing Dimension Type 2 is a method to maintain complete historical Data and Merge (Upsert) handles it easily. Hope this helps! Happy TSQL'ing :)
No comments:
Post a Comment