Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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);

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.

A MERGE statement must be terminated by a semi-colon (;)

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

like image 711
S Hanson Avatar asked Sep 15 '16 21:09

S Hanson


People also ask

Is it possible to insert multiple rows simultaneously?

Tip: Select the same number of rows as you want to insert. For example, to insert five blank rows, select five rows. It's okay if the rows contain data, because it will insert the rows above these rows. Hold down CONTROL, click the selected rows, and then on the pop-up menu, click Insert.

How do you fix the MERGE statement attempted to update or delete the same row more than once?

A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.

How many when matched clause can be used in a MERGE statement?

The MERGE statement can have, at most, two WHEN MATCHED clauses. If two clauses are specified, the first clause must be accompanied by an AND <search_condition> clause. For any given row, the second WHEN MATCHED clause is only applied if the first isn't.

Which is faster insert or MERGE?

Answer. Testing with a variety of source row sets against a target with about 6 mio. rows showed a slighty time advance using the merge command. Overall less internal steps are performed in the merge compared to delete/insert.


2 Answers

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);
like image 76
sstan Avatar answered Nov 07 '22 00:11

sstan


What I understand from msdn is that you can only insert a row for each non matching record. Do you need to use Merge? If not the following will work

WITH CTE (Sitemnum) 
AS 
(
    SELECT s.itemnum
    FROM tbl_source s
    LEFT JOIN tbl_target t ON (s.itemnum = t.itemnum)
    WHERE t.itemnum IS NULL
)
INSERT tbl_target
SELECT Sitemnum,0,99,10 
FROM CTE
UNION 
SELECT Sitemnum,0,99,80
FROM CTE
like image 39
Chapon Avatar answered Nov 07 '22 01:11

Chapon