title: Only inserting a row if it's not already there
body: <p>I had always used something similar to the following to achieve it:</p>
  
  <pre><code>INSERT INTO TheTable
  SELECT
      @primaryKey,
      @value1,
      @value2
  WHERE
      NOT EXISTS
      (SELECT
          NULL
      FROM
          TheTable
      WHERE
          PrimaryKey = @primaryKey)
  </code></pre>
  
  <p>...but once under load, a primary key violation occurred.  This is the only statement which inserts into this table at all.  So does this mean that the above statement is not atomic?</p>
  
  <p>The problem is that this is almost impossible to recreate at will.</p>
  
  <p>Perhaps I could change it to the something like the following:</p>
  
  <pre><code>INSERT INTO TheTable
  WITH
      (HOLDLOCK,
      UPDLOCK,
      ROWLOCK)
  SELECT
      @primaryKey,
      @value1,
      @value2
  WHERE
      NOT EXISTS
      (SELECT
          NULL
      FROM
          TheTable
      WITH
          (HOLDLOCK,
          UPDLOCK,
          ROWLOCK)
      WHERE
          PrimaryKey = @primaryKey)
  </code></pre>
  
  <p>Although, maybe I'm using the wrong locks or using too much locking or something.</p>
  
  <p>I have seen other questions on stackoverflow.com where answers are suggesting a "IF (SELECT COUNT(*) ... INSERT" etc., but I was always under the (perhaps incorrect) assumption that a single SQL statement would be atomic.</p>
  
  <p>Does anyone have any ideas?</p>