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.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With