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

Question

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.

Answer 1

You could write an stored procedure which copyes the records and catches any error with WHEN ANY DO statement, something like

CREATE PROCEDURE CopyRecords( ... )
AS
BEGIN
  FOR select ... FROM temp_table INTO ... DO BEGIN
     INSERT INTO target_table ...
     WHEN ANY DO BEGIN
        INSERT INTO ErrorLog(SQL_Code, GDS_Code, ... ) VALUES(SQLCODE, GDSCODE, ...);
     END
  END
END

The WHEN ... DO statement is documented in InterBase 6.0 Language Reference (scroll down the page, the IB 6 doc downloads are at the bottom), under "Procedures and Triggers" chapter.

The GDSCODE and SQLCODE context variables should help you to analyse what is exactly the reason of the error.

Answer 2

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;