MERGE Violation of PRIMARY KEY constraint

Question

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.

Answer 1

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?