如何统计SCD Type2增量加载所用T-SQL Merge的增删改操作行数

解决方案 #

你当前的场景是Merge输出同时要支撑SCD2新记录插入和操作计数，核心思路是将Merge的输出先落地到临时表，再分别执行插入和统计逻辑，完全兼容你原有业务逻辑不会影响数据正确性。

完整可运行代码 #

-- 1. 创建临时表存储Merge所有操作的输出
CREATE TABLE #MergeOutput (
    Action_Out NVARCHAR(10),
    WarehouseID INT,
    WarehouseCode NVARCHAR(50),
    WarehouseName NVARCHAR(100)
    -- 如有其他业务字段可在此处补充
)

-- 2. 执行Merge操作，输出全部写入临时表
MERGE [DWDB].[dbo].[Dim_Warehouse] AS stg
USING SourceDB.dbo.Warehouse AS SRC    
ON (stg.WarehouseID = SRC.WarehouseID) 
WHEN NOT MATCHED     
    THEN INSERT VALUES
        (
         SRC.[WarehouseID]
        ,SRC.[WarehouseCode]
        ,SRC.[WarehouseName]
        -- 补充其他需插入的业务字段
        )
WHEN MATCHED
    AND stg.dw_EndDate IS NULL
    AND stg.[WarehouseName] <> src.[WarehouseName]
    -- 如有其他需比对的业务字段可在此处补充
    THEN
        UPDATE 
        SET  stg.dw_EndDate = GETDATE()
            ,stg.dw_IsCurrent = 0 
OUTPUT $action Action_Out
        ,SRC.[WarehouseID]
        ,SRC.[WarehouseCode]
        ,SRC.[WarehouseName]
        -- 补充其他需输出的业务字段
INTO #MergeOutput;

-- 3. 执行原有SCD2逻辑：插入变更后的新有效记录
INSERT INTO [DWDB].[dbo].[Dim_Warehouse]      
(
     WarehouseID
    ,WarehouseCode
    ,WarehouseName
    -- 补充其他需插入的业务字段
)   
SELECT 
     WarehouseID
    ,WarehouseCode
    ,WarehouseName
    -- 对应补充其他业务字段
FROM #MergeOutput
WHERE Action_Out = 'UPDATE';

-- 4. 统计各操作行数
DECLARE @InsertCnt INT, @UpdateCnt INT, @DeleteCnt INT
SELECT 
    @InsertCnt = SUM(CASE WHEN Action_Out = 'INSERT' THEN 1 ELSE 0 END),
    @UpdateCnt = SUM(CASE WHEN Action_Out = 'UPDATE' THEN 1 ELSE 0 END),
    @DeleteCnt = SUM(CASE WHEN Action_Out = 'DELETE' THEN 1 ELSE 0 END)
FROM #MergeOutput

-- 可根据需求直接输出或写入ETL日志表
SELECT 
    @InsertCnt AS 新增维度记录数,
    @UpdateCnt AS 失效旧记录数,
    @DeleteCnt AS 删除记录数,
    (@InsertCnt + @UpdateCnt) AS 本次总处理记录数

-- 临时表清理
DROP TABLE #MergeOutput

逻辑说明 #

你之前的方案无法统计是因为Merge被嵌套在INSERT的子查询中，OUTPUT的结果直接被外层INSERT消费，没有留存全量操作记录用于统计。调整为输出到临时表后：

• 原有SCD2的写入逻辑完全不变，不会影响数据正确性
• 临时表留存了Merge所有操作的明细，可直接分组统计各类操作的行数
• 如果你后续需要增加硬删除逻辑（如源表删除的数据维度表也标记删除），只需要在Merge中增加WHEN NOT MATCHED BY SOURCE分支，统计逻辑无需修改即可自动获取删除行数。

内容的提问来源于stack exchange，提问作者Peter Nguy Nguyen