Possible to simulate the mySQL functionality ON DUPLICATE KEY UPDATE with SQL Server
Well, Bill beat us all, but here's a sample of what it might look like:
Merge dbo.Audience As targetUsing ( Select @VersionId As VersionId, AudienceId, 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.CreatedByPersonIdWhen Not Matched Then Insert dbo.Audience(VersionId, AudienceId, CreatedDate, CreatedByPersonId)
You should read about how to use the MERGE
statement in Microsoft SQL Server 2008. This is actually the ANSI/ISO SQL way of handling this situation (MySQL's ON DUPLICATE KEY is a proprietary MySQLism).
See docs on the MERGE
statement at MSDN.