I've got a sql file TW_popul.sql containing multiple INSERT statement:
INSERT INTO conversion_fee (FEE_ID, FEE_AMOUNT, DISCOUNT_AMOUNT, FEE_TYPE, PAYMENT_ORDER_ID)
VALUES (32353357, 8.67, 0, 'com.transferwise.fx.payment.fee.RequestFee', 29639547);
INSERT INTO conversion_fee (FEE_ID, FEE_AMOUNT, DISCOUNT_AMOUNT, FEE_TYPE, PAYMENT_ORDER_ID)
VALUES (32358776, 1.84, 0, 'com.transferwise.fx.payment.fee.RequestFee', 29643922);
INSERT INTO conversion_fee (FEE_ID, FEE_AMOUNT, DISCOUNT_AMOUNT, FEE_TYPE, PAYMENT_ORDER_ID)
VALUES (32374786, 11.26, 0, 'com.transferwise.fx.payment.fee.RequestFee', 29656743);
INSERT INTO conversion_fee (FEE_ID, FEE_AMOUNT, DISCOUNT_AMOUNT, FEE_TYPE, PAYMENT_ORDER_ID)
VALUES (32374911, 1.49, 0, 'com.transferwise.fx.payment.fee.RequestFee', 29656849);
Some of those insert statements a breaking constraint rules and throwing errors(ex. "ERROR at line 1: ORA-00001: unique constraint (TW.BT_BANK_TRANSACTION_ID_PK) violated" ).
1 row created.
1 row created.
1 row created.
INSERT INTO conversion_fee (FEE_ID, FEE_AMOUNT, DISCOUNT_AMOUNT, FEE_TYPE, PAYMENT_ORDER_ID)
*
ERROR at line 1:
ORA-00001: unique constraint (TW.CF_FEE_ID_PK) violated
1 row created.
1 row created.
How do I collect all the rows generating an error (in an Error_table for example)?
I've read about TRY… CATCH but i'm not sure it applies to catching multiple errors.
Best Answer
Maybe a better approach would be to verify if the record already exists and would violate the constraint, and only then try to insert it. You would have to script a bit around this file.
Another possibility is to insert everything to a temp table first, and then do the insert to table
conversion_fee
in batch, selectively with a WHERE/IN/EXISTS clause, rather than on a row by row basis. The temp table should simply be a copy ofconversion_fee
, without data and the constraints.Then it should not be too difficult to idenfity the duplicate rows, for example by doing a JOIN between
conversion_fee
and the temp table.With a text editor, you can easily replace the table name in your source file to something else (the name of a temp table). Import your file to that temp table, and then compare with
conversion_fee
.I can't provide full code right now, since the table structure is not known, nor is the constraint. But this is an idea.