How can I check for duplicates before inserting into a table when inserting by select

How can I check for duplicates before inserting开发者_Python百科 into a table when inserting by select:

insert into table1
select col1, col2 
from table2

I need to check if table1 already has a row with table1.col1.value = table2.col1.value, and if yes, then exclude that row from the insert.

INSERT INTO table1 
SELECT t2.col1, 
       t2.col2 
FROM   table2 t2 
       LEFT JOIN table1 t1 
         ON t2.col1 = t1.col1 
            AND t2.col2 = t1.col2 
WHERE  t1.col1 IS NULL 

Alternative using except

INSERT INTO @table2 
SELECT col1, 
       col2 
FROM   table1 
EXCEPT 
SELECT t1.col1, 
       t1.col2 
FROM   table1 t1 
       INNER JOIN table2 t2 
         ON t1.col1 = t2.col1 
            AND t1.col2 = t2.col2 

Alternative using Not Exists

INSERT INTO table2 
SELECT col1,col2 
FROM table1 t1
WHERE
NOT EXISTS( SELECT 1
    FROM table2 t2
    WHERE t1.col1 = t2.col1
          AND t1.col2 = t2.col2)

insert into table1
select col1, col2 
from table2 
where table2.col1 not in (select col1 from table1)

insert into table1
select distinct col1, col2 
from table2 

You can simply add IGNORE into your insert statement.

e.g

INSERT IGNORE INTO table1
SELECT col1, col2 
FROM table2

This is discussed here