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

Question

How can I check for duplicates before inserting 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.

Solution 1

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)

Solution 2

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

Comments

[John K.]

If you're only concerned about col1 not existing in table1 you can add that to your where clause.

[DHall]

this is probably the simplest answer but I would go with an outer join instead

[RQDQ]

This will only work when table1 doesn't have any data to start with.

[Prabhu]

If I wanted to check both col1 and col2, how could I modify this? Do I need to do the check two times?

[John K.]

There's a few ways to do that... Depending on column types and indexes on your database they may not be very efficient... 1) create a temporary result set of the items in table 2 that are already in table 1 and then insert the remaining ones (not already there) into table1; 2) If col1 and 2 are character type, you could concatenate them and then use the concatenated value as the lookup...

[Remus Rusanu]

For a longer discussion why this is wrong (as well as all the other answers posted so far), see stackoverflow.com/questions/2522379/…. Getting atomic UPSERT to work is far more trickier than this.

[Prabhu]

So why I am checking for t1.col1 is null?

[JNK]

@Prabhu - LEFT JOIN...IS NULL is a common way of checking for existing values and only inserting where they do not exist. The NULL means that there is not an existing row with that value in col1.

[Shannon Severance]

I do not see a reference to update in OP's question so I don't see this as a problem with getting an atomic UPSERT.