Edit Question - KnowledgeHub

Title Body

I am using SQL Server 2008, and would like to be able to take advantage of something like mySQL's ON DUPLICATE KEY UPDATE clause for INSERT statements Current legacy code does a delete and subsequent insert that is running into concurrency issues with duplicate key inserts from separate threads: Here is the error I see in my production environment: Violation of PRIMARY KEY constraint 'PK_Audience'. Cannot insert duplicate key in object 'dbo.Audience'. (sp_ContentUpdate) Primary Key: AudienceId, VersionId Offending SQL: DELETE FROM dbo.Audience WHERE VersionId = @VersionId IF @AudienceXml IS NOT NULL BEGIN INSERT INTO dbo.Audience ( VersionId, AudienceId, CreatedDate, CreatedByPersonId, ) SELECT @VersionId, AudienceId, GETUTCDATE(), @PersonId FROM dbo.Audience JOIN @AudienceXml.nodes('/Audiences/Audience') node(c) ON Audience.AudienceName = c.value('@Name', 'nvarchar(50)') END Wrapping this TSQL in a transaction seems to either remove the concurrency issue or mask the issue by changing the timings. However, I do not think wrapping in a transaction has actually solved the concurrency. Perhaps I am going about this wrong. Your suggestions are appreciated.

Tags (comma-separated) Cancel