Edit Question - KnowledgeHub

Title Body

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 f

Tags (comma-separated) Cancel