DWH historization tool

USE [DWH_CORE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:	
-- Description: Load procedure for dhDimensionName
--
-- =============================================

CREATE PROCEDURE [Dimension].[Load_dhPlaceholder]
    @ExecLogSID BIGINT = NULL,
    @Transferred BIGINT = 0 OUT
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        -- DUMMY UPDATE
        UPDATE Dimension.dhPlaceholder
        SET PlaceholderKey = PlaceholderKey
        WHERE 1 = 0;

        DECLARE @NASID BIGINT;
        DECLARE @NACHAR NVARCHAR(10);
        SELECT TOP 1
            @NASID = SpecialKeySID,
            @NACHAR = SpecialKeyKey
        FROM Staging.dbo.xSpecialKey
        WHERE SpecialKeyKey = 'N/A';

        DECLARE @SAPMDT NVARCHAR(3);
        SELECT TOP 1
            @SAPMDT = SAPMandantKey
        FROM Dimension.dSAPMandant
        WHERE isActiveSAPMandant = 1;

        DECLARE @rowStartDate DATETIME2(0) = GETDATE(),
        @minRowStartDate DATETIME2(0) =
        (
            SELECT TOP (1) TimeDate FROM Dimension.dTime ORDER BY TimeSID
        ),
        @maxRowEndDate DATETIME2(0) =
        (
            SELECT TOP (1) TimeDate FROM Dimension.dTime ORDER BY TimeSID DESC
        );
        DECLARE @rowEndDate DATETIME2(0) = DATEADD(SECOND, -1, @rowStartDate);

        SET IDENTITY_INSERT Dimension.dhPlaceholder ON;

        MERGE Dimension.dhPlaceholder AS dst
        USING
        (
            SELECT
            SpecialKeySID,
            SpecialKeyKey,
            SpecialKeyNameEN,
            SpecialKeyNameDE,
            HASHBYTES('SHA2_256',
            (
                SELECT SpecialKeyNameEN, SpecialKeyNameDE FOR JSON PATH)
            ) AS RowHash
            FROM Staging.dbo.xSpecialKey
            WHERE SpecialKeyDimension = 1
        ) AS src

        ON src.SpecialKeySID = dst.PlaceholderSID

        -- NOT MATCHED
        WHEN NOT MATCHED THEN
        INSERT
        (
            PlaceholderRID,
            PlaceholderSID,
            PlaceholderKey,,
            PlaceholderNameEN,
PlaceholderNameDE
            RowHash,
            RowIsCurrent,
            RowStartDate,
            RowEndDate
        )
        VALUES
        (
            src.SpecialKeySID,
            src.SpecialKeySID,
            src.SpecialKeyKey,
            PlaceholderNameEN,
PlaceholderNameDE
            src.RowHash,
            1,
            @minRowStartDate,
            @maxRowEndDate
        )

        -- MATCHED
        WHEN MATCHED AND src.RowHash <> dst.RowHash THEN
        UPDATE SET
            PlaceholderNameEN = src.SpecialKeyNameEN, PlaceholderNameDE = src.SpecialKeyNameDE,
            RowHash = src.RowHash;

        SET IDENTITY_INSERT Dimension.dhPlaceholder OFF;

        DROP TABLE IF EXISTS #output;
        SELECT TOP (0)
            CAST(0 AS SMALLINT) AS ActionType,
            PlaceholderSID AS PlaceholderRID,
            PlaceholderSID,
            PlaceholderKey,,
            PlaceholderNameEN,
PlaceholderNameDE,
            RowHash,
            RowIsCurrent,
            RowStartDate,
            RowEndDate
        INTO #output
        FROM Dimension.dhPlaceholder;

        WITH cte
        AS (
            SELECT
                x
            FROM DWH_CORE.Dimension.
        )

        MERGE Dimension.dhPlaceholder AS dst
        USING
        (
            SELECT
                PlaceholderKey = c.PlaceholderKey,
                PlaceholderNameEN = c.PlaceholderNameEN, PlaceholderNameDE = c.PlaceholderNameDE,
                HASHBYTES ('SHA2_256',
                (
                    SELECT
                        c.SpecialKeyNameEN,
c.SpecialKeyNameDE,
                    FOR JSON PATH)
                ) AS RowHash,
                1 AS RowIsCurrent,
                @rowStartDate AS RowStartDate,
                @maxRowEndDate AS RowEndDate
            FROM cte c
        ) AS src

        ON
        (
            dst.PlaceholderKey = src.PlaceholderKey
            AND dst.RowIsCurrent = 1
        )

        WHEN MATCHED AND src.RowHash <> dst.RowHash THEN
        UPDATE SET
            -- Set Current Flag to 0 and RowEndDate to Yesterday or whatever past time or date
            dst.RowIsCurrent = 0,
            dst.RowEndDate = @rowEndDate
        WHEN NOT MATCHED THEN
        INSERT
        (
            PlaceholderSID,
            PlaceholderKey,,
            PlaceholderNameEN,
PlaceholderNameDE
            RowHash,
            RowIsCurrent,
            RowStartDate,
            RowEndDate
        )

        VALUES
        (
            0,
            src.PlaceholderKey,
            src.PlaceholderNameEN,
src.PlaceholderNameDE,
            src.RowHash,
            src.RowIsCurrent,
            src.RowStartDate,
            src.RowEndDate
        )

        OUTPUT
            -- output rows from source input that will be current versions of updated rows and move them to insert
            Inserted.RowIsCurrent, -- 1=INSERTed, 0=UPDATEd, -1=DELETEd
            Inserted.PlaceholderRID,
            Inserted.PlaceholderSID,
            src.PlaceholderKey,
            src.PlaceholderNameEN,
src.PlaceholderNameDE,
            src.RowHash,
            src.RowIsCurrent,
            src.RowStartDate,
            src.RowEndDate
        INTO #output;

        SET @Transferred += @@ROWCOUNT;
        UPDATE d
            SET PlaceholderSID = o.PlaceholderRID
            FROM Dimension.dhPlaceholder d
                INNER JOIN #output o
                ON o.PlaceholderRID = d.PlaceholderRID
            WHERE o.ActionType = 1; -- INSERTED

        INSERT INTO Dimension.dhPlaceholder
        (
            PlaceholderSID,
            PlaceholderKey,,
            PlaceholderNameEN,
PlaceholderNameDE
            RowHash,
            RowIsCurrent,
            RowStartDate,
            RowEndDate
        )
        SELECT
            PlaceholderSID,
            PlaceholderKey,,
            PlaceholderNameEN,
PlaceholderNameDE
            RowHash,
            RowIsCurrent,
            RowStartDate,
            RowEndDate
        FROM #output
        WHERE ActionType = 0;

        DROP TABLE IF EXISTS #output;

        END TRY
        BEGIN CATCH
            THROW;
        END CATCH;
    END;