MERGE Statement results in a Cannot insert duplicate key in object

Question

So we have a MERGE Statement that looks as though it should do the INSERT on NOT MATCHED and the UPDATE on MATCHED correctly. However when processing for some reason it seems to be falling into the NOT MATCHED INSERT portion of the MERGE rather than the MATCHED UPDATE portion of the MERGE as I can confirm the row existence of the TARGET and SOURCE Matching criteria.

We are using a #Temporary Table we are building to perform the column INSERTs and UPDATEs accordingly. When we run the T-SQL outside of the SQL Server Stored Procedure the #Temp Table results in an empty result set; yet we are using one of the [SOURCE] data columns to UPDATE.

Can anyone explain what happens on a MERGE if the SOURCE is an empty result set? Is that what's killing this???

Answer 1

If the source is empty, then MERGE is not doing to do anything (unless you have WHEN NOT MATCHED BY SOURCE portion in your MERGE command)

Answer 2

Please post your actual MERGE statement and sample data.

Answer 3

And to clarify what Naomi says: that would typically be DELETE, but it can also be something like:

WHEN NOT MATCHED BY SOURCE
    UPDATE SET DeregDate = sysdatetime()

To mark that the row no longer is active.

But of course, there is nothing to stop you from doing

WHEN NOT MATCHED BY SOURCE
    UPDATE SET PkCol = 1

And you will get the error message in the title.

As Tom says, it would have helped if you had posted the actual code.

Answer 4

Hi，@Bobby P

Welcome to Microsoft T-SQL Q&A Forum!

Are you having problems with match and not matched records being incorrect ? The inability to insert duplicate values seems to be caused by the duplicate fields of your table records.

In order to better understand it, it is recommended that you attach a DDL statement , if the SOURCE is an empty result set , I don't quite agree with Erland's statement , If WHEN NOT MATCHED BY SOURCE clause in SQL Server MERGE statement was specified two times, one must use an update operation and another one must use delete operation.

Yes, that means update can be done when there is no match, this is determined by your actual needs.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.