Matei Zoc Matei Zoc - 4 months ago 17
SQL Question

sql - how to insert on table1 from table2 but dont insert the duplicate value

i have 2 table, table1 and table2, each of them have rows added table1 = 2m rows and table2 = 1m rows and some rows from table2 is already on table1, and table1 have unique value

i try this command:

INSERT INTO table1 (top1, top2, top3)
SELECT top1, top2, top3 FROM table2


top1 is unique value, how i can make the selection so if table2 top1 is already on table1 top1 to pass next?

Answer

There are several ways to do this, and some different databases provide easier methods. Here's a generic solution with not exists:

INSERT INTO table1 (top1, top2, top3)
SELECT top1, top2, top3 FROM table2 t2
WHERE NOT EXISTS (
    select 1 
    from table1 t1 
    where t1.top1 = t2.top1)

Another generic option with an outer join \ null check:

insert into table1 (top1, top2, top3)
select t2.top1, t2.top2, t2.top3
from table2 t2 
   left join table1 t1 on t2.top1 = t1.top1
where t1.top1 is null
Comments