Clever Geek Handbook

How to copy records between tables only if they are valid according to validation restrictions in Firebird / Interbase?

I want to copy a bunch of records from the temp table to the target table. The problem is that some records might violate check constraints on the target table, so I want to insert as much as possible and generate error logs elsewhere for invalid records.

If I execute:

INSERT INTO target_table
  SELECT ... FROM temp_table

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

+3


source to share


2 answers


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;
+1


source


You can write a stored procedure that copies records and catches any error using a statement WHEN ANY DO

, 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 statement is WHEN ... DO

documented in the InterBase 6.0 Language Reference (scroll down the page, downloads IB 6 documents at the bottom), in the Procedures and Triggers chapter.

GDSCODE

and the SQLCODE

context variables should help you analyze what exactly is causing the error.

+6


source






More articles:


All Articles