1

I use an Informix database. I access the data of a table via the tool "dbaccess". I have currently saved data from a table via UNLOAD command to a CSV file.

I want to import the data into the integration environment into a table. However, some records already exist in my table.

Is there any way to skip records that already exist in the target table so that I don't have to delete that record from the import file and restart the data import in dbaccess via LOAD command?

Error when importing an existing record into a table:

268: Unique constraint (informix.pk_column_a) violated.   100: ISAM error:  duplicate value for a record with unique key.
847: Error in load file row 1.

Example for an UNLOAD to a csv file:

UNLOAD TO /myfolder/example_file.csv DELIMITER ";"
SELECT * FROM example_table
;

Example for loading a file to a table:

LOAD FROM /myfolder/example_file.csv DELIMITER ";"
INSERT INTO target_table
;
Viktor Künstler
  • 185
  • 9
  • 1
    Have you looked at the MERGE statement? – Jonathan Leffler Mar 17 '23 at 13:09
  • Hi @JonathanLeffler, i can't find an example where the LOAD statement and the MERGE statement are used together. Do you have an example? – Viktor Künstler Mar 17 '23 at 13:40
  • 1
    There probably isn't a way to use MERGE with LOAD. However, you could LOAD the data into a temporary table, or treat the data file as an external table, and then run MERGE on that temporary table and real target table. – Jonathan Leffler Mar 17 '23 at 14:23

1 Answers1

2

An alternative to using the MERGE statement with the LOAD statement and a temporary table could be to use violations tables and deferred constraints. Data rows that violate the constraints are then inserted into the violations table, whose name defaults to <table>_vio.

An example using the customer table from the stores_demo database:

UNLOAD TO customer.unl SELECT * FROM customer;
START VIOLATIONS TABLE FOR customer;
SET CONSTRAINTS FOR customer FILTERING WITHOUT ERROR;
LOAD FROM customer.unl INSERT INTO customer;
SET CONSTRAINTS FOR customer ENABLED;
STOP VIOLATIONS TABLE FOR customer;

The LOAD statement will insert no rows into the customer table as they all violate the primary key constraint. These rows appear in customer_vio.

Note that when the violations table is stopped the customer_vio and customer_dia tables remain and will need to be manually dropped when no longer required.

Simon Riddle
  • 976
  • 4
  • 4