DB2: Insert new rows and ignore duplicates

I have about 100 rows to insert in a table - Some of them have already been inserted before and some of them have not

This is my insert that works fine if the primary key doesn't exist.. I'm going to run this 100 time with different values each time.. However, if the primary key exist, it fails and stop future commands to run.

How to ignore the failure and keep going or simply ignore duplicates?

INSERT  INTO  MY_TABLE 
   VALUES( 12342, 'fdbvdfb', 'svsdv', '5019 teR','' , 'saa', 'AL',35005 , 'C', 37, '0',368 , 'P', '2023-02-13', '2023-01-01', '2023-01-10', '2023-01-20','' , 'Test', 'Test', 'Test', 'JFK', '', null, 'Y', 'Y', '', '', '', '', '', '',2385 ,2 , '', 'N', '2023-01-16', '2023-01-20', '', NULL,NULL, NULL, NULL, 'Y', 'Test', 'Test', '', 'N', 'Test', '')

This is the error:

SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "XPS01ME1" constrains

Insert IGNORE into throws:

The use of the reserved word "IGNORE" following "" is not valid.

If it can help I'm using WinSQL 10.0.157.697

Solution

You don't mention what platform and version of Db2, so I'll point you to the Linux/Unix/Windows (LUW) documentation for the MERGE statement...

Since I don't know your table or column names, I'll just give you an example with dummy names.

merge into MYTABLE as tgt
using (select * 
        from table( values(1,1,'CMW',5,1)
                  ) tmp ( tblKey, fld1, fld2, fld3, fld4)
      ) as src
on src.tblKey = tgt.tblekey
when not matched then 
   insert ( tblKey, fld1, fld2, fld3, fld4)
     values ( src.tblKey, src.fld1, src.fld2, src.fld3, src.fld4);

You're basically building a temporary table on the fly of one row
table( values(1,1,'CMW',5,1) ) tmp ( tblKey, fld1, fld2, fld3, fld4)

Then if there's no matching record via on src.tblKey = tgt.tblekey you do an insert.

Note that while you could do this 100 times, it is a much better performing solution to do all 100 rows at a time.

merge into MYTABLE as tgt
using (select * 
        from table( values (1,1,'CMW1',5,1)
                          , (2,11,'CMW2',50,11)
                          , (3,21,'CMW3',8,21)
                         -- , <more rows here>
                  ) tmp ( tblKey, fld1, fld2, fld3, fld4)
      ) as src
on src.tblKey = tgt.tblekey
when not matched then 
   insert ( tblKey, fld1, fld2, fld3, fld4)
     values ( src.tblKey, src.fld1, src.fld2, src.fld3, src.fld4);

Optionally, you could create an actual temporary table, insert the 100 rows (preferably in a single insert) and then use MERGE.