ignore insert of rows that violate duplicate key index

Question

I perform an insert as follows:

INSERT INTO foo (a,b,c)
   SELECT x,y,z
   FROM fubar
   WHERE ...

However, if some of the rows that are being inserted violate the duplicate key index on foo, I want the database to ignore those rows, and not insert them and continue inserting the other rows.

The DB in question is Informix 11.5. Currently all that happens is that the DB is throwing an exception. If I try to handle the exception with:

ON EXCEPTION IN (-239)
END EXCEPTION WITH RESUME;

... it does not help because after the exception is caught, the entire insert is skipped.

I don't think informix supports INSERT IGNORE, or INSERT ... ON DUPLICATE KEY..., but feel free to correct me if I am wrong.

Answer 1

Use IF statement and EXISTS function to check for existed records. Or you can probably include that EXISTS funtion in the WHERE clause like below

INSERT INTO foo (a,b,c) 
SELECT x,y,z 
FROM fubar 
WHERE (NOT EXISTS(SELECT a FROM foo WHERE ...))

Answer 2

I don't know about Informix, but with SQL Server, you can create an index, make it unique and then set a property to have it ignore duplicate keys so no error gets thrown on a duplicate. It's just ignored. Perhaps Informix has something similar.

Answer 3

Depending on whether you want to know all about all the errors (typically as a result of a data loading operation), consider using violations tables.

START VIOLATIONS TABLE FOR foo;

This will create a pair of tables foo_vio and foo_dia to contain information about rows that violate the integrity constraints on the table.

When you've had enough, you use:

STOP VIOLATIONS TABLE FOR foo;

You can clean up the diagnostic tables at your leisure. There are bells and whistles on the command to control which table is used, etc. (I should perhaps note that this assumes you are using IDS (IBM Informix Dynamic Server) and not, say, Informix SE or Informix OnLine.)

Violations tables are a heavy-duty option - suitable for loads and the like. They are not ordinarily used to protect run-of-the-mill SQL. For that, the protected INSERT (with SELECT and WHERE NOT EXISTS) is fairly effective - it requires the data to be in a table already, but temp tables are easy to create.

Answer 4

There are a couple of other options to consider.

IDS 11.50 supports the MERGE statement. This could be used to insert rows from fubar where the corresponding row in foo does not exist, and to update the rows in foo with the values from fubar where the corresponding row already exists in foo (the duplicate key problem).

Another way of looking at it is:

SELECT fubar.*
   FROM fubar JOIN foo ON fubar.pk = foo.pk
   INTO TEMP duplicate_entries;

DELETE FROM fubar WHERE pk IN (SELECT pk FROM duplicate_entries);

INSERT INTO foo SELECT * FROM fubar;

...processs duplicate_entries

DROP TABLE duplicate_entries

This cleans the source table (fubar) of the duplicate entries (assuming it is only the primary key that is duplicated) before trying to insert the data. The duplicate_entries table contains the rows in fubar with the duplicate keys - the ones that need special processing in some shape or form. Or you can simply delete and ignore those rows, though in my experience, that is seldom a good idea.