SQL Insert record if doesn't exist

sql, sql-insert, sql-server-2008-r2

Solution

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.

Problem

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

Original source