Ignore duplicate key errors in Insert from select statement SQL

Question

Is there a way or a command to ignore duplicated primary key error when inserting data from a select?

Explanation Lets suppose i have this query: Insert into my_table values (select * from my_second_table) but my_table has a primary key on id. Also my_second_table has a column named id so when attempting the insert it cause a duplicated primary key error.

I know i can avoid these kind of problems using go but in this case i cant use it because is an insert from a select, or at least is what i know.

Is there something i can do?

Answer 1

Different databases have different methods for conflict resolution. The following works in most databases:

Insert into my_table values ( . . . )  -- you should always list all the columns
     select . . .   -- you should list all the columns
     from my_second_table t2
     where not exists (select 1 from my_table t where t.id = t2.id);

This is not a perfect solution, because race conditions could cause problems. But it will work if no other data modification queries run on the server while this is being executed.