Informix/SQL - Load data from CSV and insert if doesn't already exist

Question

I am writing an sql script in informix that loads data from a csv file into a temporary table, and then will insert data if it doesn't already exist.

The csv has 3 columns and is inserted into a temp table called temp_table_csv:

temp_table_csv
msg_group
code
message

'code' corresponds to the same code field in table1 and table1.ID is a foreign key to table.code_id.

table1
ID         serial         PK
code       varchar(255)   FK
msg_group  varchar(255)

table2
lang_id    serial         PK
code_id    varchar(255)   FK
msg_pt1    varchar(255)
msg_pt2    varchar(255)

If the code in the temporary table exists in table1, then I want to ignore it.

Otherwise, if it does not exist:

A new entry in table1 should be added with a new ID and the msg_group from the temp table. Also table2 should be updated with the code_id being the table1.ID and msg_pt1 being the message.

I have got the data into the temp table and i'm not sure how I can check to see if the 'ID' exists now.

Does table2 have an ID column too? If not, how are msg_pt1 and msg_pt2 associated with the ID in the database? If table2 has an ID column, is there a foreign key relationship between table1 and table2 such that if a row appears in table2, it must also appear in table1? Can rows appear in table1 without having an entry in table2? Are there any other columns in table1? Which version of Informix are you using? And on which platform (o/s and version)? Do you have the MERGE statement available? — Jonathan Leffler
I'm not sure how important MERGE is — I'm still meditating on how to process it. You need two separate insert operations, which makes life harder than if you're inserting into a single table. — Jonathan Leffler
@JonathanLeffler Hi, I've updated the problem to be a lot clearer. It makes it difficult because there's 3 tables rather than 2. — ChrisBrown202o
Since the types of table1.ID and table2.code_ID are different (SERIAL vs VARCHAR), it is hard to see how you make table2.code_ID into a foreign key referencing table1.ID. — Jonathan Leffler

Jmo Jmo · Accepted Answer · 2018-12-08T21:52:10

Informix... maybe i'm wrong but it smell like an Avaya product, anyway. Without any samples, i would suggest you to try something like

INSERT INTO table2(field1, field2)
SELECT msg_pt1, msg_pt2
FROM #TEMPORARY_CSV_OR_WHATEVER T
WHERE T.ID NOT IN(SELECT ID FROM table1)

INSERT INTO table1(ID)
SELECT ID
FROM #TEMPORARY_CSV_OR_WHATEVER T
WHERE T.ID NOT IN(SELECT ID FROM table1)

Informix/SQL - Load data from CSV and insert if doesn't already exist

1 Answers