SQL Server存储过程实现行合并：指定ID合并并过滤重复Ident-val记录

需求规则 #

• 传入两个ID（如目标ID @TargetID=101、待合并ID @MergeID=102）
• 规则1：若两个ID下存在Ident和val均完全相同的记录，合并时忽略待合并ID的该条记录
• 规则2：待合并ID的其余记录，执行以下操作：
  • 将ID改为目标ID
  • closedate 设为 GETDATE()
  • IsActive 设为0

表结构 #

CREATE TABLE MDM (
    ID INT,
    Ident VARCHAR(50),
    val VARCHAR(50),
    closedate DATETIME,
    IsActive BIT
)

测试数据 #

INSERT INTO MDM VALUES
(101, 'Name', 'Alice', NULL, 1),
(101, 'Age', '30', NULL, 1),
(102, 'Name', 'Alice', NULL, 1),
(102, 'City', 'New York', NULL, 1),
(102, 'Phone', '123456', NULL, 1)

原有MERGE存储过程问题 #

原有尝试的MERGE逻辑未准确过滤Ident+val重复的记录，不符合规则要求：

-- 原有尝试的MERGE（存在问题）
CREATE PROCEDURE MergeMDM
    @TargetID INT,
    @MergeID INT
AS
BEGIN
    MERGE INTO MDM AS Target
    USING (SELECT * FROM MDM WHERE ID = @MergeID) AS Source
    ON Target.ID = @TargetID AND Target.Ident = Source.Ident AND Target.val = Source.val
    WHEN MATCHED THEN
        DELETE
    WHEN NOT MATCHED THEN
        INSERT (ID, Ident, val, closedate, IsActive)
        VALUES (@TargetID, Source.Ident, Source.val, GETDATE(), 0);
END

修正后的存储过程实现 #

CREATE PROCEDURE MergeMDM_Corrected
    @TargetID INT,
    @MergeID INT
AS
BEGIN
    SET NOCOUNT ON;

    -- 步骤1：删除待合并ID中与目标ID Ident+val完全重复的记录（符合规则1）
    DELETE m
    FROM MDM m
    WHERE m.ID = @MergeID
    AND EXISTS (
        SELECT 1 
        FROM MDM t
        WHERE t.ID = @TargetID
        AND t.Ident = m.Ident
        AND t.val = m.val
    );

    -- 步骤2：更新剩余的待合并记录，改为目标ID并设置状态字段（符合规则2）
    UPDATE m
    SET 
        m.ID = @TargetID,
        m.closedate = GETDATE(),
        m.IsActive = 0
    FROM MDM m
    WHERE m.ID = @MergeID;
END

预期输出 #

执行存储过程 EXEC MergeMDM_Corrected 101, 102 后，MDM表数据如下：

ID  | Ident | val     | closedate           | IsActive
101 | Name  | Alice   | NULL                | 1
101 | Age   | 30      | NULL                | 1
101 | City  | New York| 2024-XX-XX XX:XX:XX | 0
101 | Phone | 123456  | 2024-XX-XX XX:XX:XX | 0

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