SQL Insert record if doesn't exist

Question

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:

Returns 29 results:

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.

This returns no results (I would expect this to return 34):

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)

This returns 63:

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)

ps. using SQL Server 2008 R2

Answer 1

Scrap your inner joins in your subqueries.

What you should be trying is the following:

 SELECT PRODUCT_CODE FROM #no_stock
 WHERE NOT EXISTS (SELECT * from NO_STOCK 
              WHERE NO_STOCK.PRODUCT_CODE = #no_stock.PRODUCT_CODE)

You don't need an inner join here because the #no_stock table is referenced in the outer query.

The same is true for your other query:

 SELECT PRODUCT_CODE FROM #no_stock
 WHERE EXISTS (SELECT * from NO_STOCK 
          WHERE NO_STOCK.PRODUCT_CODE = #no_stock.PRODUCT_CODE)

Try these and see if they work.

Answer 2

You dont need both exists and inner join in case your product_code defines records in both tables.

So I suppose you are using temp table as a reference and adding record to table from temp table only in case it only exists in temp table

So your query should be something like

 insert into no_stock
 select * from #no_stock
 where product_code not in (select product_code from no_stock)