Will the entire query fail when trying to insert a duplicate key?

Question

If I have a query like this:

INSERT INTO map
    SELECT 5, product_id FROM products WHERE price < 500 )

Say this tries to insert 300 rows into the map table. Say 20 of the inserts generate duplicate key errors. Will the other 280 rows be inserted or will the entire query fail?

If so, what is the best way to write this query so that the non-duplicate rows are inserted and the duplicates are ignored?

Answer 1

The query will fail at first duplicate key. If you want to continue inserting next use

INSERT IGNORE INTO map (map_id, product_id)
SELECT  5, product_id FROM products WHERE price < 500

When inserting multiple rows like this, do not put the query into VALUES().

Answer 2

You could first create a temporary table storing the rows that you already know the do not exist in the destination table, then you are able to insert the rows in destination table by using a simple insert into: Transact-SQL:

SELECT 5, product_id FROM products INTO #TEMP
    WHERE price < 500 AND 
      NOT EXISTS ( SELECT 1 FROM map m, products p WHERE m.product_id = p.product_id )
INSERT INTO map SELECT * FROM #TEMP