CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sqlsql-server-2008
Prabhu
Prabhu

Reputation: 13325

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

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.

Upvotes: 12

Views: 42701

Answers (4)

Andy A
Andy A

Reputation: 4301

You can simply add IGNORE into your insert statement.

e.g

INSERT IGNORE INTO table1
SELECT col1, col2 
FROM table2

This is discussed here

Upvotes: -1

Conrad Frix
Conrad Frix

Reputation: 52645

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)

Upvotes: 21

Sergei Golos
Sergei Golos

Reputation: 4350

insert into table1
select distinct col1, col2 
from table2

Upvotes: -1

John K.
John K.

Reputation: 5474

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

Upvotes: 2

Related Questions