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
     

CodePudding user response：

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