Template - Type 6 SCD Stored Procedure

,

This handy template has template parameters (Query, Specifiy Values for Template Parameters) - use that instead of search and replace.

The template has two merge statements with descriptive comments in brackets.

The Type 6 SCD uses 2 merges:

Type 1 (changes on non-historical fields, deleted source records)

Type 2 (changes on historical fields and new records)

This will make it easier to create the processes as they are here as shells and you won't need to remember the syntax.

Many thanks to Adam Aspin's wonderful article for teaching me about the OUTPUT portion of a MERGE.

http://www.sqlservercentral.com/articles/MERGE/73805/

USE <DB Name, sysname, Database_Name>;
GO

IF OBJECTPROPERTY(OBJECT_ID('dbo.<Proc Name, sysname, Procedure_Name>'),N'IsProcedure') IS NULL 
BEGIN
    EXECUTE ('Create Procedure dbo.<Proc Name, sysname, Procedure_Name> As Print ''Hello World!''')
    RAISERROR('Procedure <Proc Name, sysname, Procedure_Name> created.', 10, 1);
END;
GO

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO

ALTER PROCEDURE dbo.<Proc Name, sysname, Procedure_Name>
AS 
/*********************************************************************************
    Name:       <Proc Name, sysname, Procedure_Name>

    Author:     [Your name]

    Purpose:    Type 6 Slowly Changing Dimension

    Notes:      

    Called by:  

    Date        User    Proj    Description
    ----------------------------------------------------------------------------
    2014-XX-XX  XXX     XXXXX   Initial Release
*********************************************************************************
EXEC dbo.<Proc Name, sysname, Procedure_Name>;
*********************************************************************************/
SET NOCOUNT ON;
SET XACT_ABORT ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;

BEGIN

    BEGIN TRY

    DECLARE 
         @EffectiveDt DATETIME      = GETDATE()
        ,@ExpirationDt DATETIME     = '2999-12-31';

        /* Start a new transaction */
        BEGIN TRANSACTION;

        -- Type 1 SCD

        MERGE [3 part qualified table] AS target
        USING [3 part qualified table] AS source
        ON [fields]
        WHEN MATCHED AND
        CHECKSUM([type 2 fields]) = CHECKSUM([type 2 fields])
        THEN UPDATE
        -- Fields used for Type 2 are removed from the update
        SET
            [Update without EffectiveDt, ExpirationDt, CurrentRow]
        WHEN NOT MATCHED BY SOURCE THEN UPDATE
        SET
            ExpirationDt = @EffectiveDt;

        -- Type 6 SDC

        INSERT INTO [3 part qualitifed table] 
        (
            [Fields]
        )
        SELECT
            [Fields]
            ,@EffectiveDt
            ,@ExpirationDt
            ,'Current'
        FROM
        (
            MERGE [3 part qualitifed table] AS target
            USING [3 part qualified table] AS source
            ON [fields]
            WHEN NOT MATCHED THEN INSERT
            (
                [fields]
            )
            VALUES
            (
                [fields - Current record]
            )
            WHEN MATCHED
                AND CurrentRow = 'Current'
                AND CHECKSUM([Type 2 fields]) <> CHECKSUM([type 2 fields])
            THEN UPDATE
            SET 
                 ExpirationDt = @EffectiveDt
                ,CurrentRow = 'Expired'
            OUTPUT 
                [Table fields without EffectiveDt, ExpirationDt, CurrentRow]
                ,$Action AS MergeAction
        ) AS mrg
        WHERE mrg.MergeAction = 'UPDATE';

        /* If you have an open transaction, commit it */
        IF @@TRANCOUNT > 0
            COMMIT TRANSACTION;

    END TRY
    BEGIN CATCH

        /* Whoops, there was an error... rollback! */
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;

    END CATCH;

    SET NOCOUNT OFF;
    RETURN 0;
END
GO

SET QUOTED_IDENTIFIER OFF;
GO

IF OBJECTPROPERTY(OBJECT_ID('dbo.<Proc Name, sysname, Procedure_Name>'), N'IsProcedure') = 1 
    RAISERROR('Procedure <Proc Name, sysname, Procedure_Name> was successfully updated.', 10, 1);
ELSE
    RAISERROR('Procedure <Proc Name, sysname, Procedure_Name> FAILED to create!', 16, 1);
GO

Rate

4.5 (2)

Share

Share

Rate

4.5 (2)