如何修改MERGE查询实现成员数据版本化更新并插入新版记录

你要实现的是典型的*Type 2 缓慢变化维（SCD2）*逻辑，核心是保留历史数据版本，单条MERGE语句无法对同一条匹配行同时执行更新和插入操作，这里给两种可直接落地的实现方案：

方案一：分UPDATE+INSERT两步实现（兼容性最强，易维护） #

第一步：更新待变更的旧版本记录的结束时间 #

UPDATE member_staging x
SET enddate = CAST(GETDATE() AS DATE)
-- Oracle环境替换为：SET enddate = TRUNC(SYSDATE)
FROM members y
WHERE x.member_id = y.member_id
  -- 只处理当前有效的最新版本记录
  AND x.enddate = '9999-12-31'
  -- 匹配属性有变更的记录
  AND (
    x.first_name <> y.first_name 
    OR x.last_name <> y.last_name 
    OR x.rank <> y.rank
  );

第二步：插入新版本记录 #

包含两类数据：完全新增的成员、属性有变更需新增版本的老成员

INSERT INTO member_staging (member_id, first_name, last_name, rank, startdate, enddate)
SELECT 
  y.member_id,
  y.first_name,
  y.last_name,
  y.rank,
  -- 新版本开始时间比旧版本结束时间晚1天，避免区间重叠
  DATEADD(DAY, 1, CAST(GETDATE() AS DATE)),
  -- Oracle环境替换为：TRUNC(SYSDATE) + 1
  '9999-12-31'
FROM members y
-- 过滤掉无需新增版本的记录：已经存在有效版本且属性完全一致
WHERE NOT EXISTS (
  SELECT 1 FROM member_staging x
  WHERE x.member_id = y.member_id
    AND x.enddate = '9999-12-31'
    AND x.first_name = y.first_name
    AND x.last_name = y.last_name
    AND x.rank = y.rank
);

方案二：单MERGE语句实现 #

如果必须用单条语句完成，可以构造拆分后的数据源，将需要更新+插入的成员拆为两条记录分别匹配对应操作：

MERGE INTO member_staging x
USING (
  -- 构造用于更新旧版本的数据源
  SELECT 
    y.member_id,
    y.first_name,
    y.last_name,
    y.rank,
    'UPDATE' AS op_type,
    CAST(GETDATE() AS DATE) AS new_enddate,
    NULL AS new_startdate
  FROM members y
  JOIN member_staging x_old 
    ON x_old.member_id = y.member_id
    AND x_old.enddate = '9999-12-31'
    AND (x_old.first_name <> y.first_name OR x_old.last_name <> y.last_name OR x_old.rank <> y.rank)
  
  UNION ALL

  -- 构造用于插入新版本的数据源
  SELECT 
    y.member_id,
    y.first_name,
    y.last_name,
    y.rank,
    'INSERT' AS op_type,
    NULL AS new_enddate,
    DATEADD(DAY, 1, CAST(GETDATE() AS DATE)) AS new_startdate
  FROM members y
  WHERE NOT EXISTS (
    SELECT 1 FROM member_staging x_cur
    WHERE x_cur.member_id = y.member_id
      AND x_cur.enddate = '9999-12-31'
      AND x_cur.first_name = y.first_name
      AND x_cur.last_name = y.last_name
      AND x_cur.rank = y.rank
  )
) y
ON (
  x.member_id = y.member_id 
  AND x.enddate = '9999-12-31'
  AND y.op_type = 'UPDATE'
)
WHEN MATCHED THEN
  UPDATE SET x.enddate = y.new_enddate
WHEN NOT MATCHED THEN
  INSERT (member_id, first_name, last_name, rank, startdate, enddate)
  VALUES (y.member_id, y.first_name, y.last_name, y.rank, y.new_startdate, '9999-12-31');

注意事项 #

• 所有日期建议做截断处理（去掉时分秒），避免时间精度问题导致区间重叠
• 建议给member_staging表添加(member_id, enddate)联合唯一约束，避免同一个成员同时出现多条有效记录
• Oracle环境下将GETDATE()替换为SYSDATE，DATEADD(DAY, 1, 日期)替换为日期 + 1即可

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