12

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.

Prabhu
  • 12,995
  • 33
  • 127
  • 210

4 Answers4

21
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)
Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • So why I am checking for t1.col1 is null? – Prabhu Apr 08 '11 at 20:04
  • 2
    @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`. – JNK Apr 08 '11 at 20:15
2
insert into table1
select col1, col2 
from table2 
where table2.col1 not in (select col1 from table1)
John K.
  • 5,426
  • 1
  • 21
  • 20
  • If you're only concerned about col1 not existing in table1 you can add that to your where clause. – John K. Apr 08 '11 at 19:28
  • this is probably the simplest answer but I would go with an outer join instead – DHall Apr 08 '11 at 19:30
  • If I wanted to check both col1 and col2, how could I modify this? Do I need to do the check two times? – Prabhu Apr 08 '11 at 19:35
  • 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... – John K. Apr 08 '11 at 19:46
  • For a longer discussion why this is wrong (as well as all the other answers posted so far), see http://stackoverflow.com/questions/2522379/atomic-upsert-in-sql-server-2005/2525343#2525343. Getting atomic UPSERT to work is far more trickier than this. – Remus Rusanu Apr 08 '11 at 20:00
  • 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. – Shannon Severance Apr 08 '11 at 21:23
-1

You can simply add IGNORE into your insert statement.

e.g

INSERT IGNORE INTO table1
SELECT col1, col2 
FROM table2

This is discussed here

Community
  • 1
  • 1
Andy A
  • 4,191
  • 7
  • 38
  • 56
-1
insert into table1
select distinct col1, col2 
from table2
Sergei Golos
  • 4,342
  • 1
  • 18
  • 21