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 
