title: What is the best practice for inserting a record if it doesn't already exist?
body: <p>I know at least three ways to insert a record if it doesn't already exist in a table:</p>
  
  <ol>
  <li><p>The first one is using if not exist:</p>
  
  <pre><code>IF NOT EXISTS(select 1 from table where &lt;condition&gt;)
      INSERT...VALUES
  </code></pre></li>
  <li><p>The second one is using merge:</p>
  
  <pre><code>MERGE table AS target  
  USING (SELECT values) AS source 
  ON (condition)  
  WHEN NOT MATCHED THEN  
  INSERT ... VALUES ...
  </code></pre></li>
  <li><p>The third one is using insert...select:</p>
  
  <pre><code>INSERT INTO table (&lt;values list&gt;)
  SELECT &lt;values list&gt;
  WHERE NOT EXISTS(select 1 from table where &lt;condition&gt;)
  </code></pre></li>
  </ol>
  
  <p>But which one is the best?</p>
  
  <p>The first option seems to be not thread-safe, as the record might be inserted between the select statement in the if and the insert statement that follows, if two or more users try to insert the same record.</p>
  
  <p>As for the second option, merge seems to be an overkill for this, as the <a href="https://msdn.microsoft.com/en-us/library/bb510625.aspx" rel="nofollow noreferrer">documentation</a> states:</p>
  
  <blockquote>
    <p><strong>Performance Tip:</strong> The conditional behavior described for the MERGE statement works best when the two tables have a complex mixture of matching characteristics. For example, inserting a row if it does not exist, or updating the row if it does match. When simply updating one table based on the rows of another table, improved performance and scalability can be achieved with basic INSERT, UPDATE, and DELETE statements. </p>
  </blockquote>
  
  <p>So I think the third option is the best for this scenario (only insert the record if it doesn't already exist, no need to update if it does), but I would like to know what SQL Server experts think.</p>
  
  <p>Please note that after the insert, I'm not interested to know whether the record was already there or whether it's a brand new record, I just need it to be there so that I can carry on with the rest of the stored procedure.</p>