sql insert into table from select without duplicates (need more then a DISTINCT)

Question

I am selecting multiple rows and inserting them into another table. I want to make sure that it doesn't already exists in the table I am inserting multiple rows into. DISTINCT works when there are duplicate rows in the select, but not when comparing it to the data already in the table your inserting into.

If I Selected one row at a time I could do a IF EXIST but since its multiple rows (sometimes 10+) it doesn't seem like I can do that.

Answer 1

INSERT INTO target_table (col1, col2, col3)
SELECT DISTINCT st.col1, st.col2, st.col3
FROM source_table st
WHERE NOT EXISTS (SELECT 1 
                  FROM target_table t2
                  WHERE t2.col1 = st.col1 
                    AND t2.col2 = st.col2
                    AND t2.col3 = st.col3)

If the distinct should only be on certain columns (e.g. col1, col2) but you need to insert all column, you will probably need some derived table (ANSI SQL):

INSERT INTO target_table (col1, col2, col3)
SELECT st.col1, st.col2, st.col3
FROM ( 
     SELECT col1, 
            col2, 
            col3, 
            row_number() over (partition by col1, col2 order by col1, col2) as rn
     FROM source_table 
) st
WHERE st.rn = 1
AND NOT EXISTS (SELECT 1 
                FROM target_table t2
                WHERE t2.col1 = st.col1 
                  AND t2.col2 = st.col2)

Answer 2

If you already have a unique index on whatever fields need to be unique in the destination table, you can just use INSERT IGNORE (here's the official documentation - the relevant bit is toward the end), and have MySQL throw away the duplicates for you.

Hope this helps!