MERGE Violation of PRIMARY KEY constraint
sql, sql-server, sql-server-2008, t-sql, upsert
Solution
You need to add a `HOLDLOCK` on your `MERGE` statement. Try the following:
MERGE INTO Assets WITH (HOLDLOCK) AS Target
...
This avoids the race condition that you are running into. See more info here
EDIT
Based on your update, the only other thing I can think of is that your temp table might have a duplicate record in it. Can you double check?
Problem
I have a SQL Server 2008 many-to-many relationship table (`Assets`) with two columns: ``` AssetId (PK, FK, uniqueidentifier, not null) AssetCategoryId (PK, FK, int, not null) ``` In my project, I need to take rows from this table, and insert them into a replicated database periodically. So, I have two databases that are exactly the same (constraints included). In order to "copy" from one database to the other, I use a MERGE statement with a temp table. I insert up to 50 records into the temp table, then merge the temp table with the `Assets` table I am copying into as follows: ``` CREATE TABLE #Assets (AssetId UniqueIdentifier, AssetCategoryId Int); INSERT INTO #Assets (AssetId, AssetCategoryId) VALUES ('ed05bac3-7a92-46aa-8822-2d882b137597', 44), ('dc5e3082-e2eb-4bdf-a640-94e0f59411ed', 22) ... ; MERGE INTO Assets WITH (HOLDLOCK) AS Target USING #Assets AS Source ON Target.AssetId = Source.AssetId AND Target.AssetCategoryId = Source.AssetCategoryId WHEN MATCHED THEN UPDATE SET ... WHEN NOT MATCHED BY Target THEN INSERT (AssetId,AssetCategoryId) VALUES (Source.AssetId,Source.AssetCategoryId); ``` This works great, for the most part. However, once in a while, I get the error: Violation of PRIMARY KEY constraint 'PK_Assets'. Cannot insert duplicate key in object 'dbo.Assets'. The duplicate key value is (dc5e3082-e2eb-4bdf-a640-94e0f59411ed, 22). The statement has been terminated. When I check in the `Assets` table, no such record exists... so I am confused how I would be inserting a duplicate key. Any idea what is going on here? UPDATE When testing, it runs successfully 6 times, inserting 300 rows. On the 7th try, it always gives the same error shown above. Furthermore, when I `INSERT` `(dc5e3082-e2eb-4bdf-a640-94e0f59411ed, 22)` by itself, it works fine. My test is then able to continue and insert the remaining rows with no errors.