Can we get INSERT UPDATE Count from Slowly Changing Dimension Type-2 stored procedure?

Question

How to get

Insert Count = ? Update Count = ?

in slowly changing dimension type 2 ?

Here is my procedure

CREATE PROCEDURE dbo.MergeDimCustomer
AS
BEGIN
    DECLARE @CurrentDateTime datetime
    DECLARE @MinDateTime datetime
    DECLARE @MaxDateTime datetime
     
    SELECT
        @CurrentDateTime = cast(getdate() as datetime),
        @MinDateTime = cast('1900-01-01' as datetime),
        @MaxDateTime = cast('9999-12-31' as datetime)
    
    -- SCD1
    
    MERGE [dim].[CustomerPhone] as [target]
    USING
    (
        SELECT
            [Address],
            [Id],
            [Name],
            [Telephone]
        FROM [stg].[CustomerPhone]
    ) as [source]
    ON
    (
        [source].[Id] = [target].[Id]
    )
     
    WHEN MATCHED AND
    (
        ([target].[EndDate] = @MaxDateTime OR ([target].[EndDate] IS NULL AND @MaxDateTime IS NULL))
    )
    AND
    (
        ([source].[Name] <> [target].[Name] OR ([source].[Name] IS NULL AND [target].[Name] IS NOT NULL) OR ([source].[Name] IS NOT NULL AND [target].[Name] IS NULL)) OR
        ([source].[Telephone] <> [target].[Telephone] OR ([source].[Telephone] IS NULL AND [target].[Telephone] IS NOT NULL) OR ([source].[Telephone] IS NOT NULL AND [target].[Telephone] IS NULL))
    )
    AND
    (
        ([source].[Address] = [target].[Address] OR ([source].[Address] IS NULL AND [target].[Address] IS NULL))
    )
    THEN UPDATE
    SET
        [target].[Name] = [source].[Name],
        [target].[ScdVersion] = [target].[ScdVersion] + 1,
        [target].[Telephone] = [source].[Telephone]
    ;
    
    -- SCD2
    
    INSERT INTO [dim].[CustomerPhone]
    (
        [Address],
        [EndDate],
        [Id],
        [Name],
        [ScdVersion],
        [StartDate],
        [Telephone]
    )
    SELECT
        [Address],
        [EndDate],
        [Id],
        [Name],
        [ScdVersion],
        [StartDate],
        [Telephone]
    FROM
    (
        MERGE [dim].[CustomerPhone] as [target]
        USING
        (
            SELECT
                [Address],
                [Id],
                [Name],
                [Telephone]
            FROM [stg].[CustomerPhone]
        ) as [source]
        ON
        (
            [source].[Id] = [target].[Id]
        )
        WHEN NOT MATCHED BY TARGET
        THEN INSERT
        (
            [Address],
            [EndDate],
            [Id],
            [Name],
            [ScdVersion],
            [StartDate],
            [Telephone]
        )
        VALUES
        (
            [Address],
            @MaxDateTime,
            [Id],
            [Name],
            1,
            @MinDateTime,
            [Telephone]
        )
    WHEN MATCHED AND
    (
        ([EndDate] = @MaxDateTime OR ([EndDate] IS NULL AND @MaxDateTime IS NULL))
    )
    AND
    (
        ([target].[Address] <> [source].[Address] OR ([target].[Address] IS NULL AND [source].[Address] IS NOT NULL) OR ([target].[Address] IS NOT NULL AND [source].[Address] IS NULL))
    )
        THEN UPDATE
        SET
            [EndDate] = @CurrentDateTime
        OUTPUT
            $Action as [MERGE_ACTION_91025df2-1344-4e4e-84f7-e790d1f77d7c],
            [source].[Address] AS [Address],
            @MaxDateTime AS [EndDate],
            [source].[Id] AS [Id],
            [source].[Name] AS [Name],
            INSERTED.[ScdVersion] + 1 AS [ScdVersion],
            @CurrentDateTime AS [StartDate],
            [source].[Telephone] AS [Telephone]
     
    ) MERGE_OUTPUT
    WHERE MERGE_OUTPUT.[MERGE_ACTION_91025df2-1344-4e4e-84f7-e790d1f77d7c] = 'UPDATE' 
        AND MERGE_OUTPUT.[Id] IS NOT NULL
    ;
    END
    GO
 

Answer 1

You can do it this way:

.....

    THEN UPDATE
            SET
                [target].[Name] = [source].[Name],
                [target].[ScdVersion] = [target].[ScdVersion] + 1,
                [target].[Telephone] = [source].[Telephone]
    
    OUTPUT
       inserted.*,
       deleted.*;

Upgrade your code based on this post: the output clause for the merge statements