幂等MERGE示例失效求助：如何实现幂等性插入处理？

问题说明 #

需调整MERGE语句实现幂等性插入（仅插入目标表中不存在的记录），遵循指定模板逻辑，解决当前生成重复待插入行的问题。

示例数据 #

create table #baseset
(
 Date varchar(30),
 ID varchar(30),
 State varchar(30),
 Count varchar(30)
)
insert into #baseset values('2/7/2023', 'A', 'A',   null)
insert into #baseset values('2/6/2023', 'A', 'A',   null)

create table #changeset
(
 Date varchar(30),
 ID varchar(30),
 State varchar(30),
 Count varchar(30)
)
insert into #changeset values('2/8/2023',   'A', 'A',   null)
insert into #changeset values('2/7/2023',   'A', 'A',   null)

当前问题 #

原MERGE语句的USING部分通过JOIN #baseset生成待插入行时，会因变更表单条记录与基础表多条同ID记录匹配，导致生成重复的mergeKey=null行，最终staged_updates包含5条记录，不符合预期。

调整后的MERGE语句 #

核心修改是在第二部分查询中添加DISTINCT去重，避免生成重复行：

MERGE INTO #baseset
USING (
  -- 尝试匹配ID，用于判断是否存在该ID的记录
  SELECT
    #changeset.ID as mergeKey, #changeset.*
  FROM
    #changeset
  UNION ALL
    -- 插入ID已存在但Date不存在的记录，设置mergeKey为NULL强制不匹配
  SELECT DISTINCT
    NULL as mergeKey,
    #changeset.*
  FROM
    #changeset
    JOIN #baseset ON #changeset.ID = #baseset.ID
  WHERE
    #changeset.Date <> #baseset.Date
) staged_updates
ON #baseset.id = mergeKey
WHEN NOT MATCHED BY TARGET THEN 
  INSERT (Date, ID, State, Count)
  VALUES (staged_updates.Date, staged_updates.ID, staged_updates.State, staged_updates.Count)
;

验证结果 #

调整后staged_updates的输出符合预期：

+--------+--------+---+-----+-----+
|mergeKey|    Date| ID|State|Count|
+--------+--------+---+-----+-----+
|       A|2/8/2023|  A|    A| null|
|       A|2/7/2023|  A|    A| null|
|    null|2/8/2023|  A|    A| null|
+--------+--------+---+-----+-----+

执行MERGE后，目标表仅新增2/8/2023的记录，重复执行时不会再次插入，实现幂等性。

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