使用未匹配的合并插入多行 [英] Inserting multiple rows with Merge NOT MATCHED

问题描述

MERGE tbl_target t
USING tbl_source s
ON t.itemnum = s.itemnum
WHEN NOT MATCHED 
    INSERT (itemnum, minqty, maxqty, parent)
    VALUES (s.itemnum,0,99,10),(s.itemnum,0,99,80);

如果某项在目标上不存在但在源头上确实存在，我试图在目标表上插入两行.每次尝试SQL Server时，VALUES之间的'，'都会出现错误.

I'm trying to Insert two rows on the target table if an item does not exist on target but does exist on the source. Everytime I try SQL server gives an error on the ',' between the VALUES.

MERGE语句必须以分号(;)

是否可以在MERGE语句中进行多行插入?

Is it possible to do multi-row inserts in a MERGE statement?

推荐答案

可以通过调整USING子句为每个tbl_source.itemnum值返回多个行:

It is possible by tweaking the USING clause to return multiple rows per tbl_source.itemnum value:

MERGE tbl_target t
USING (
  select s.itemnum, 
         0 as minqty, 
         99 as maxqty,
         p.parent 
    from tbl_source s
    cross join (
      select 10 as parent
      union all
      select 80 as parent) p
) s
ON t.itemnum = s.itemnum
WHEN NOT MATCHED THEN
    INSERT (itemnum, minqty, maxqty, parent)
    VALUES (s.itemnum,s.minqty,s.maxqty,s.parent);

这篇关于使用未匹配的合并插入多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！