title: SQL Server - How to insert a record and make sure it is unique
body: <p>I'm trying to figure out the best way to insert a record into a single table but only if the item doesn't already exist. The KEY in this case is an NVARCHAR(400) field. For this example, lets pretend it's the name of a <em>word</em> in the Oxford English Dictionary / insert your fav dictionary here. Also, i'm guessing i will need to make the Word field a primary key. (the table will also have a unique identifier PK also).</p>
  
  <p>So .. i might get these words that i need to add to the table...</p>
  
  <p>eg.</p>
  
  <ul>
  <li>Cat </li>
  <li>Dog </li>
  <li>Foo</li>
  <li>Bar</li>
  <li>PewPew </li>
  <li>etc...</li>
  </ul>
  
  <p>So traditionally, i would try the following (pseudo code)</p>
  
  <pre><code>SELECT WordID FROM Words WHERE Word = @Word
  IF WordID IS NULL OR WordID &lt;= 0
      INSERT INTO Words VALUES (@Word)
  </code></pre>
  
  <p>ie. <em>If the word doesn't exist, then insert it.</em></p>
  
  <p>Now .. the problem i'm worried about is that we're getting LOTS of hits .. so is it possible that the word could be inserted from another process in between the SELECT and the INSERT .. which would then throw a constraint error? (ie. a <a href="http://en.wikipedia.org/wiki/Race_condition" rel="noreferrer" title="Race Condition">Race Condition</a>).</p>
  
  <p>I then thought that i might be able to do the following ...</p>
  
  <pre><code>INSERT INTO Words (Word)
  SELECT @Word
  WHERE NOT EXISTS (SELECT WordID FROM Words WHERE Word = @Word)
  </code></pre>
  
  <p>basically, <em>insert a word when it doesn't exist.</em></p>
  
  <p>Bad syntax aside, i'm not sure if this is bad or good because of how it locks down the table (if it does) and is not that performant on a table that it getting massive reads and plenty of writes.</p>
  
  <p>So - what do you Sql gurus think / do?</p>
  
  <p>I was hoping to have a simple insert and 'catch' that for any errors thrown.</p>