Cannot insert duplicate key row in object

Question

Post reply

Cannot insert duplicate key row in object

chris.o.smith

SSCommitted

Points: 1674
More actions
May 2, 2018 at 3:51 pm

#410473

Hi,
I'm on SQL Server 2014 enterprise edition. I'm running into the following error when attempting to run a MERGE statement: "Cannot insert duplicate key row in object 'TABLENAME' with unique index 'INDEX_NAME'. The duplicate key value is (###, ###).". The strange thing is that the index that is referenced ("INDEX_NAME") is not a unique index, and it doesn't have 2 key columns as indicated in the error message. The definition of INDEX_NAME is as follows:
CREATE NONCLUSTERED INDEX [INDEX_NAME] ON [TABLE_NAME] ([FK_Column1] ASC)INCLUDE ([FK_Column2])
In addition, if I rebuild the index mentioned in the error message and then re-run the same exact MERGE statement, it succeeds. I'm lost as to what is going on here. The only thing that I can see that is a little different with this table is that the PK column is set up as a unique index, and the clustered index is on a different column (this design is out of my control, so its not an option to change it and see if it resolves the issue).
Any ideas on what is happening here?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Joe Torre SSChampion Points: 10248 More actions · Answer 1

Please check this out. You may need to apply SQL Server 2014 Cumulative Update 4 or above

chris.o.smith SSCommitted Points: 1674 More actions · Answer 2

Thanks - that certainly sounds a lot like my issue, but we're currently on SQL Server 2014 SP2 CU4 (12.0.5540.0)...

rajborntodare Hall of Fame Points: 3576 More actions · Answer 3

Hi Chris,

Just to give an Idea, did you check the original column of the FK column , if it is making any inserts there.
Also check the properties of the index for option Ignore Duplicate values ;p

chris.o.smith SSCommitted Points: 1674 More actions · Answer 4

Thanks, yes, we've thoroughly checked all of this and no luck. If we drop and recreate the same exact index, and then run the same exact MERGE statement with the same exact underlying data, it works.

It also works if we change the structure of the table so that the clustered index is on the PK column. So, to resolve my issue, I'm pushing to have the structure of the table changed permanently so that the clustered index is on the PK. This seems like an MS bug to me (sounds very similar to the link that Joe Torre posted above).

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Sounds like a lot of work and problems with MERGE have and (apparently) continue with it. I just do good ol' fashioned "Upserts" and call the job done.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)