This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(297.6, 40%, 38%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ER%3C/text%3E%3C/svg%3E)
Hi,
I am using SQL 2019 Enterprise Version. I have a simple process that runs with no errors updating / merging between tables in the same database. If i enable change tracking on one of the destination tables the process starts to fail with the error Cannot insert duplicate key row in object ' XXXXX ' with unique index '. As soon as i disable the change tracking everything works fine again. There are no errors in the log.
Anyone else experienced this issue, or know how to resolve the problem?
Thank you!
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
and just to add, there is no key conflicts or unique indexes. When the process runs with no change tracking everything works. As soon as change tracking is enabled the above error appears.
What is that XXXXX? Is that one your own tables you have masked out, or is it one of the internal tables that Change Tracking uses?
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(163.2, 84%, 25%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYM%3C/text%3E%3C/svg%3E)
Hi @Robert ,
Welcome to Microsoft Q&A!
Not sure if it is due to some reason between the merge statement and change tracking.Maybe you can show the sql statements and simple table structure examples and data so that I can try to reproduce this problem in my own environment.
Sorry yes, thats the XXXX is our table name not the internal table.
I'll see if i can try to provide a sample of the query and the table, but the merge statement is just a normal merge statement updating/inserting from a temp table. We run it several times during the day and there are no issues. As soon as we add change tracking on then we get errors about a unique index duplicate key even though we have no unique indexes applied.
Just to add an update, we removed all the merge statements and are just using updates and inserts, and still seeing the same message. We also upgraded to the latest cu12 and still seeing the same issue.
I'm reaching out to Ms today hopefully to get some assistance on it.
It certainly sounds like a bug in the product.
I'm afraid that we cannot do much here. Well, we could ask you to post a repro that demonstrates the issue, but that could take you a bit of time. And in the end we might only say "yes, we can repro this", but unless there is something that stands out as special, I don't think that we will be able to help you to get around the problem.
So I would advice you that you first check "SELECT @@version" and verify that you are on CU11 of SQL 2019. If you are not, apply CU11, which is the most recent CU for SQL 2019, to see if that it helps.
If it does not, open a support case, if this is a blocking issue for you. (And I guess it is.)
Well, an alternative is of course to replace the MERGE with individual INSERT and UPDATE statements, and there are blog posts out there that gives MERGE bad press. But I find MERGE quite nifty myself, so I am not keen on that workaround.
Thank you for your reply - Yes we are on CU11, and plan to raise a support case hopefully today as it is starting to block. Hopefully we can resolve it before replacing our merges though to inserts/updates and i feel it is a bug either with SQL or with how its handling our code possibly. Will provide an update once we know more too.