MERGE语句中使用INSERT *添加额外列报错的问题排查

你的问题出在MERGE语句的when not matched then insert部分，MERGE语法不支持insert *, 额外字段这种写法——虽然普通SELECT查询允许用select *, ...追加字段，但MERGE的INSERT子句有严格的语法规则：要么插入USING子查询返回的所有字段（需确保USING结果和目标表列匹配），要么明确列出目标表列名并逐个指定对应值。

修复方案一：调整USING子查询，预包含插入所需字段 #

把要插入的is_current、start_date、end_date提前放到USING的子查询里，这样INSERT时直接用insert *即可匹配目标表结构：

merge into target_table as tgt
using
(
  select 
    src.id as mergekey,
    src.*,
    1 as is_current,
    '2020-01-09' as start_date,
    null as end_date
  from source_table src
  union all
  select
    null as mergekey,
    src.*,
    1 as is_current,
    '2020-01-09' as start_date,
    null as end_date
  from source_table src
  join target_table tgt
    on tgt.id = src.id
    and tgt.is_current = 1
  where not( tgt.name = src.name )
) us
on tgt.id = us.mergekey
when matched
  and tgt.is_current = 1
  and not ( tgt.name = us.name )  -- 修正原代码中src.name的上下文错误
then update set
  is_current = 0,
  end_date = '2020-01-08'
when not matched then
insert *

修复方案二：明确指定目标表列和对应值 #

如果不想调整USING子查询，可以直接列出Target表的所有列，然后逐个赋值：

merge into target_table as tgt
using
(
  select 
    src.id as mergekey,
    src.*
  from source_table src
  union all
  select
    null as mergekey,
    src.*
  from source_table src
  join target_table tgt
    on tgt.id = src.id
    and tgt.is_current = 1
  where not( tgt.name = src.name )
) us
on tgt.id = us.mergekey
when matched
  and tgt.is_current = 1
  and not ( tgt.name = us.name )  -- 修正原代码中src.name的上下文错误
then update set
  is_current = 0,
  end_date = '2020-01-08'
when not matched then
insert (id, name, -- 替换为Target表的实际列名，按顺序列出所有列
        is_current, start_date, end_date)
values (us.id, us.name, -- 对应source表的字段
        1, '2020-01-09', null)

额外注意点 #

原代码中when matched条件里的not ( tgt.name = src.name )会报错，因为src在当前上下文已不存在，需改成us.name（USING子查询的别名是us）。

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