我正在使用sql Server 2008,并希望能够利用像
MySQL的ON DUPLICATE KEY UPDATE子句之类的INSERT语句
当前的遗留代码执行一个删除和后续插入,它与单独线程中的重复键插入运行并发问题：
这是我在生产环境中看到的错误：
Violation of PRIMARY KEY constraint 'PK_Audience'. Cannot insert duplicate key in object 'dbo.Audience'.
(sp_ContentUpdate)
首要的关键：
AudienceId,VersionId
违规sql：
DELETE FROM dbo.Audience
WHERE VersionId = @VersionId
IF @AudienceXml IS NOT NULL
BEGIN
INSERT INTO dbo.Audience (
VersionId,AudienceId,CreatedDate,CreatedByPersonId,)
SELECT @VersionId,GETUTCDATE(),@PersonId
FROM dbo.Audience
JOIN @AudienceXml.nodes('/Audiences/Audience') node(c)
ON Audience.AudienceName = c.value('@Name','nvarchar(50)')
END
将此Tsql包含在事务中似乎可以通过更改时序来删除并发问题或掩盖问题.但是,我不认为在事务中包装实际上已经解决了并发问题.
也许我这个错了.您的建议不胜感激
解决方法
那么,比尔打败了我们,但这里是一个可能的样子：
Merge dbo.Audience As target
Using (
Select @VersionId As VersionId,GetUtcDate() As CreatedDate,@PersonId As CreatedByPersonId
From dbo.Audience
Join @AudienceXml.nodes('/Audiences/Audience') node(c)
On Audience.AudienceName = c.value('@Name','nvarchar(50)')
)
When Matched Then
Update
Set VersoinId = target.VersionId,Audience = target.AudienceId,CreatedDate = target.CreatedDate,CreatedByPersionId = target.CreatedByPersonId
When Not Matched Then
Insert dbo.Audience(VersionId,CreatedByPersonId)
