I know how to make an insert of a row of values into a table using insert into. However, I cannot find an appropriate idiom for accomplishing the task that I have.
I have two tables. One table is the primary table and the other is an additional table. I need to insert rows that do not exist in the primary from additional table ignoring any duplicates.
I am trying to use some variations of:
replace into primary
select * from additional;
But this obviously replaces the rows which is not what I want. Should I use on duplicate somehow or am I in a completely wrong direction?
EDIT:
Both of the columns have a unique surrogate key column which is just an integer value.
If you have a unique key, then the following will generate an error on the duplicate keys:
insert into primary(col1, . . .)
select col1, . . .
from secondary;
Here are three ways to avoid this error. The first is insert ignore:
insert ignore into primary(col1, . . .)
select col1, . . .
from secondary;
The problem with insert ignore is that it ignores all errors. So, you might miss something important other than the duplicate unique key error.
The second is on duplicate key update. You need to put in a "no-op" update statement:
insert into primary(col1, . . .)
select col1, . . .
from secondary
on duplicate key update col1 = col1;
This is often the easiest way.
The third is to have join or not exists logic:
insert into primary(col1, . . .)
select col1, . . .
from secondary
where not exists (select 1 from primary where primary.keycol = secondary.keycol);
I think this can result in race conditions if two queries are inserting rows at the same time.