title: Possible to simulate the mySQL functionality ON DUPLICATE KEY UPDATE with SQL Server
body: <p>I am using SQL Server 2008, and would like to be able to take advantage of something like mySQL's ON <code>DUPLICATE KEY UPDATE</code> clause for <code>INSERT</code> statements</p>
  
  <p>Current legacy code does a delete and subsequent insert that is running into concurrency issues with duplicate key inserts from separate threads:</p>
  
  <p>Here is the error I see in my production environment:</p>
  
  <pre><code>Violation of PRIMARY KEY constraint 'PK_Audience'. Cannot insert duplicate key in object 'dbo.Audience'.
  </code></pre>
  
  <p>(sp_ContentUpdate)</p>
  
  <p>Primary Key:</p>
  
  <pre><code>AudienceId, VersionId
  </code></pre>
  
  <p>Offending SQL:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.  </p>
  
  <p>Perhaps I am going about this wrong.  Your suggestions are appreciated.</p>