如何从Type-2缓慢变化维度存储过程获取插入、更新计数？

针对你提供的MergeDimCustomer存储过程，我们可以通过捕获MERGE操作的输出结果和利用@@ROWCOUNT系统函数，精准统计SCD1更新数、SCD2新客户插入数、SCD2版本新增数三类核心计数，具体修改后的完整存储过程如下：

CREATE PROCEDURE dbo.MergeDimCustomer
AS
BEGIN
    DECLARE @CurrentDateTime datetime
    DECLARE @MinDateTime datetime
    DECLARE @MaxDateTime datetime
    -- 声明计数变量，初始化0
    DECLARE @SCD1UpdateCount INT = 0
    DECLARE @SCD2NewCustomerCount INT = 0
    DECLARE @SCD2VersionAddCount INT = 0

    SELECT
        @CurrentDateTime = cast(getdate() as datetime),
        @MinDateTime = cast('1900-01-01' as datetime),
        @MaxDateTime = cast('9999-12-31' as datetime)

    -- 临时表：捕获SCD1 MERGE操作结果
    CREATE TABLE #SCD1MergeResults (ActionType NVARCHAR(10))

    -- SCD1：更新当前版本客户的Name/Telephone
    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]
    -- 输出操作类型到临时表
    OUTPUT $Action INTO #SCD1MergeResults;

    -- 统计SCD1更新记录数
    SELECT @SCD1UpdateCount = COUNT(*) FROM #SCD1MergeResults WHERE ActionType = 'UPDATE'
    -- 清理临时表
    DROP TABLE #SCD1MergeResults

    -- 临时表：捕获SCD2 MERGE所有操作结果
    CREATE TABLE #SCD2MergeResults (
        ActionType NVARCHAR(10),
        Address VARCHAR(255),
        EndDate DATETIME,
        Id INT,
        Name VARCHAR(255),
        ScdVersion INT,
        StartDate DATETIME,
        Telephone VARCHAR(20)
    )

    -- 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 ActionType,
            [source].[Address] AS [Address],
            @MaxDateTime AS [EndDate],
            [source].[Id] AS [Id],
            [source].[Name] AS [Name],
            CASE WHEN $Action = 'UPDATE' THEN INSERTED.[ScdVersion] + 1 ELSE 1 END AS [ScdVersion],
            CASE WHEN $Action = 'UPDATE' THEN @CurrentDateTime ELSE @MinDateTime END AS [StartDate],
            [source].[Telephone] AS [Telephone]
    ) MERGE_OUTPUT
    WHERE MERGE_OUTPUT.ActionType = 'UPDATE' 
        AND MERGE_OUTPUT.Id IS NOT NULL;

    -- 统计SCD2新客户插入数：MERGE中NOT MATCHED的INSERT操作数量
    SELECT @SCD2NewCustomerCount = COUNT(*) 
    FROM #SCD2MergeResults 
    WHERE ActionType = 'INSERT'

    -- 统计SCD2版本新增数：外层INSERT的行数，即因地址变化新增的客户版本数
    SELECT @SCD2VersionAddCount = @@ROWCOUNT

    -- 输出所有统计结果
    SELECT
        @SCD1UpdateCount AS SCD1_更新记录数,
        @SCD2NewCustomerCount AS SCD2_新客户插入数,
        @SCD2VersionAddCount AS SCD2_版本新增数

    -- 清理临时表
    DROP TABLE #SCD2MergeResults
END
GO

计数说明 #

• SCD1_更新记录数：统计维度表中当前活跃版本（EndDate为最大值）的客户，其Name或Telephone字段发生变化的更新次数。
• SCD2_新客户插入数：统计首次从源表同步到维度表的新客户数量，对应MERGE操作中NOT MATCHED BY TARGET的插入行为。
• SCD2_版本新增数：统计因客户Address字段变化，而新增的维度表版本记录数量，对应MERGE中UPDATE旧版本后，外层INSERT新增的新版本记录数。

内容的提问来源于stack exchange，提问作者Vedant Desai