Search code examples
databasefirebirdinterbasecheck-constraints

How can I copy records between tables only if they are valid according to check constraints in Firebird/Interbase?

I want to copy a bunch of records from a temp table to a target table. The problem is that some records may violate check constraints in the target table so I want to insert everything that is possible and generate error logs somewhere else for the invalid records.

If I execute:

INSERT INTO target_table
  SELECT ... FROM temp_table

nothing would be inserted if any record violates any constraint. I could make a loop and manually insert one by one, but I think the performance would be lower.

Solution

  • Like this:

    INSERT INTO
    some_table1 (fld1, fld2, fld3)
SELECT
    some_table2.fld1,
    some_table2.fld2,
    some_table2.fld3
FROM
    some_table2
WHERE
    some_table2.fld > 100
LIMIT
    5;