How to insert multiple rows in a merge?

Question

How to insert multiple rows in a merge in SQL? I'm using a MERGE INSERT and I'm wondering is it possible to add two rows at the same time? Under is the query I have written, but as you can see, I want to insert both boolean for IsNew, also when it is not matched, I want to add a row for IsNew = 1 and one IsNew = 0. How can I achieve this?

MERGE ITEMS AS TARGET
USING @table AS SOURCE
ON T.[ID]=S.ID
WHEN MATCHED THEN
    UPDATE SET 
        T.[Content] = S.[Content],
WHEN NOT MATCHED THEN
    INSERT (ID, Content, TIME, IsNew)
    VALUES (ID, TEXT, GETDATE(), 1),

I think the SQL dialect is very important here. Is it MySQL, SQLServer or something else? — Non Plus Ultra, Jul 09 '19 at 09:18
I added the `sql-server` and `tsql` tags based on the (non standard) usage of square brackets and `@` in an identifier — , Jul 09 '19 at 09:19
@a_horse_with_no_name I think it is SQL Server as I use the SQL Server Management — Ken Kaneki, Jul 09 '19 at 09:22
Does Items (target table) have pair of rows before the merge operation? Can you provide some sample data of source tables and the expected output? — Han, Jul 09 '19 at 09:43
This is duplicate...https://stackoverflow.com/questions/39520529/inserting-multiple-rows-with-merge-not-matched — cybernetic87, Jul 09 '19 at 09:45
@cybernetic87 Is it? Because I can't make that answer to work with my user defined table. — Ken Kaneki, Jul 09 '19 at 09:48
Your update statement makes no sense - you are setting the target id to the value of the source id when they are equal - it's just a useless update. You can simply use an insert statement instead. — Zohar Peled, Jul 09 '19 at 10:01

score 2 · Answer 1 · answered Jul 09 '19 at 10:24

You can't do this directly with a merge statement, but there is a simple solution.

The merge statement <merge_not_matched> clause (which is the insert...values|default values) clause can only insert one row on the target table for each row in the source table.
This means that for you to enter two rows for each match, you simply need to change the source table - in this case, it's as simple as a cross join query.

However the <merge_matched> clause requires that only a single row from the source can match any single row from the target - or you will get the following error:

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. 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.

To solve this problem you will have to add a condition to the when match to make sure only one row from the source table updates the target table:

MERGE Items AS T
USING (
    SELECT Id, Text, GetDate() As Date, IsNew
    FROM @table
    -- adding one row for each row in source
    CROSS JOIN (SELECT 0 As IsNew UNION SELECT 1) AS isNewMultiplier
       ) AS S
    ON T.[ID]=S.ID
WHEN MATCHED AND S.IsNew = 1 THEN -- Note the added condition here
    UPDATE SET 
        T.[Content] = S.[Text] 
WHEN NOT MATCHED THEN
    INSERT (Id, Content, Time, IsNew) VALUES 
    (Id, Text, Date, IsNew);

You can see a live demo on rextester.

With all that being said, I would like to refer you to another stackoverflow post that offers a better alternative then using the merge statement.
The author of the answer is a Microsoft MVP and an SQL Server expert DBA, you should at least read what he has to say.

score 0 · Answer 2 · answered Jul 09 '19 at 09:44

It seems you can't achieve this using a merge statement. It may be better for you to split the two into separate queries for update and insert.

For example:

UPDATE ITEMS SET ITEMS.ID = @table.ID FROM ITEMS INNER JOIN @table ON ITEMS.ID = @table.ID

INSERT INTO ITEMS (ID, Content, TIME, IsNew) SELECT (ID, TEXT, GETDATE(), 1) FROM @table
INSERT INTO ITEMS (ID, Content, TIME, IsNew) SELECT (ID, TEXT, GETDATE(), 0) FROM @table

This will insert both rows as desired, mimicking your merge statement. However, your update statement won't do much - if you're matching based on ID, then it's impossible for you to have any IDs to update. If you wanted to update other fields, then you could change it like this:

UPDATE ITEMS SET ITEMS.Content = @table.TEXT FROM ITEMS INNER JOIN @table ON ITEMS.ID = @table.ID

How to insert multiple rows in a merge?

2 Answers2