sql - insert or ignore rows that violate foreign key constraints sqlite

Question

I am trying to insert many rows into my table, some of which violate a foreign key constraint on that table. I want to SKIP adding the rows that violate this constraint and add all the correct rows successfully. However, I get an error even if I use insert or ignore in my query.

Is there any way to do this in SQLite?

Answer 1

You can use not exists to filter in the insert statement:

insert into table2 ( . . . )
    select . . . 
    from table1 t1
    where exists (select 1
                  from table2 t2
                  where t2.pk = t1.t2pk
                 );

That is, don't even attempt the insert.