SQL Merge failing to insert then update two identical rows from the target table

Question

I am trying to merge two tables using SQL Merge, in the below script:

 BEGIN TRAN;
DECLARE @T TABLE(Id BigInt);
MERGE Target AS T
USING Source AS S
ON (T.ObjectName = S.ObjectName) 
WHEN NOT MATCHED BY TARGET 
     THEN INSERT(ObjectName,Value,[TimeStamp],Quality) VALUES(S.ObjectName, S.Value,S.[TimeStamp],S.Quality)
WHEN MATCHED 
    THEN UPDATE SET 
    T.Value = S.Value,
    T.Quality=S.Quality

OUTPUT S.Id INTO @T;
DELETE Source
WHERE Id in (SELECT Id
                     FROM @T);
if @@Error > 0
Rollback
else
COMMIT Tran
GO

What I am trying to do is to insert new records from "Target" to "Source", and the "Matched" records will be updated in the "Source" Table. The issue I am facing is that sometimes, the source table has two identical "Not Matched Rows". According to the script logic and to my requirement, it should insert the first "Not Matched", then the second "Not Matched" will need to be treated as an update, not as an insert, since it is now a "Matched" row, because we have already inserted the first record.

It seems that my merge is working as one bulk script, where the first insert is not noticed when the script reaches to the second identical row. Is this how "SQL Merge" works or is it form my script?

Thanks

score 4 · Accepted Answer · answered Jul 18 '13 at 06:59

4

Assuming the row with the later timestamp should "win", why not eliminate the duplicate as part of your SOURCE query:

;With Selection as (
    select ObjectName,Value,Quality,
      ROW_NUMBER() OVER (PARTITION BY ObjectName ORDER BY Timestamp desc) as rn,
      MIN(Timestamp) OVER (PARTITION BY ObjectName) as Timestamp
    from
      Source
)
MERGE Target AS T
USING (select * from Selection where rn=1) AS S
--The rest is unaltered
...

And this is not something unusual about MERGE - all DML statements act "as if" all rows are processed simultaneously.

answered Jul 18 '13 at 06:59

Damien_The_Unbeliever

234,701
27
340
448

Thanks for your answer, I will try it right now and get back to you – Hassan Mokdad Jul 18 '13 at 07:02
1

I wouldn't be asking for "why not", I'd say that it is how it have to be done. @HassanMokdad You should always filter your source before merge statement. If there are duplicate rows in source - that does not match both will be inserted (possibly causing PK error in target) - http://sqlfiddle.com/#!6/d9257/6 If there are duplicates that already exists in target, `MERGE` fails with error (unable to update same row twice) - http://sqlfiddle.com/#!6/81f90/1 – Nenad Zivkovic Jul 18 '13 at 07:07
@Nendad, you are right, I am facing a PK error. and in other cases I got the error "Unable to update same row twice". Now I understood the SQL Merge better, thanks – Hassan Mokdad Jul 18 '13 at 07:10
Thanks Damien, it wokred perfectly – Hassan Mokdad Jul 18 '13 at 07:41

SQL Merge failing to insert then update two identical rows from the target table

1 Answers1