CODEWITHSUNDEEP
sql-servert-sqlsql-merge
Ken Kaneki
Ken Kaneki

Reputation: 57

How to insert multiple rows in a merge?

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

Upvotes: 2

Views: 3809

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82474

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.

Upvotes: 3

reembank
reembank

Reputation: 106

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

Upvotes: 0

Related Questions