title: SQL Insert record if doesn't exist
body: <p>What I am trying to do is compare a table with another temporary table and if the record does not already exist insert it into the table. My problem is the IF NOT EXIST doesn't seem to be correct. If i pull the code apart the problem I have is:</p>
  
  <p>Returns 29 results:</p>
  
  <pre><code>SELECT * 
  from NO_STOCK INNER JOIN #no_stock
    ON NO_STOCK.PRODUCT_CODE = #no_stock.PRODUCT_CODE 
  WHERE NO_STOCK.PRODUCT_CODE = #no_stock.PRODUCT_CODE.
  </code></pre>
  
  <p>This returns no results (I would expect this to return 34):</p>
  
  <pre><code>SELECT PRODUCT_CODE
  FROM #no_stock
  WHERE NOT EXISTS
    (SELECT * from NO_STOCK INNER JOIN #no_stock 
      ON NO_STOCK.PRODUCT_CODE = #no_stock.PRODUCT_CODE 
     WHERE NO_STOCK.PRODUCT_CODE = #no_stock.PRODUCT_CODE)
  </code></pre>
  
  <p>This returns 63:</p>
  
  <pre><code>SELECT PRODUCT_CODE
  FROM #no_stock
  WHERE EXISTS 
    (SELECT * from NO_STOCK INNER JOIN #no_stock 
       ON NO_STOCK.PRODUCT_CODE = #no_stock.PRODUCT_CODE 
     WHERE NO_STOCK.PRODUCT_CODE = #no_stock.PRODUCT_CODE)
  </code></pre>
  
  <p>ps. using SQL Server 2008 R2</p>