Multiple Merge Statements in the Single procedure

Question

HI All,

I am getting an error in the SQL sevrer managed instance that the temp db is getting full. Upon investigation i identified that the procedure contains a merge statement.

In the Merge statement, there are 5 UNION ALL queries written.

When i break the merge into 5 sepeerate merge statements, all the queries are executed without any issues.

I tried update the procedure and add the 5 MErges in the below format:

BEGIN
MERGE 1
MErGE 2
MErge 3
Merge 4
MErge 5
END

But when i execute it via the procedure it gives me an error:

Operation on target UpsertSalesByPartnerInventory failed: Execution fail against sql server. Sql error number: 8672. Error Message: 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

Please advise on how can i get this corrected.

Answer 1

It seems that (at least) one of the MERGE statements has a poor join condition in the USING clause.

A row in the target should be matched by at most one row in the source, and ideally this should clear from that you are joining on keys. If there can be multiple matches, SQL Server calls this an error, rather than just picking one.

Here is a simple example which you can tell is flawed, and indeed produces this error.

CREATE TABLE target (a int NOT NULL PRIMARY KEY,
                     data varchar(18) NOT NULL)
go
INSERT target (a, data) 
    VALUES(1, 'Data')
go
CREATE TABLE source (a int NOT NULL,
                     b int NOT NULL,
                     data varchar(18) NOT NULL,
                     PRIMARY KEY(a, b)
)
INSERT source (a, b, data)
   VALUES(1, 1, 'New data'),
         (1, 2, 'Newer data'),
         (1, 3, 'Newest data')
go
MERGE target t
USING source s ON t.a = s.a
WHEN NOT MATCHED THEN 
   INSERT (a, data) VALUES(s.a, s.data)
WHEN  MATCHED THEN 
   UPDATE SET data = s.data
;
go
DROP TABLE target, source