How to skip unique constraint violation

Question

If I have bulk insert as a part of transaction , like this :

cmdTxt.Clear();
cmdTxt.Append(" INSERT INTO  sc1pen ");
cmdTxt.Append(" SELECT action_month,action_year,200,emp_num,penalty_action , ");
cmdTxt.Append("  'APPLY ' || penalty_reason || ' day ' , 0 , 0 ");
cmdTxt.Append(" FROM sc2pen WHERE sal_year = ? and sal_month = ? and penalty_type = 1 and pay_type = 0 ");
myIfxCmd.CommandText = cmdTxt.ToString();

myIfxCmd.Parameters.Clear();

myIfxCmd.Parameters.Add("sal_year", IfxType.Integer);
myIfxCmd.Parameters.Add("sal_month", IfxType.Integer);

myIfxCmd.Parameters[0].Value = penaltyDt.Rows[0]["sal_year"];
myIfxCmd.Parameters[1].Value = penaltyDt.Rows[0]["sal_month"];

myIfxCmd.ExecuteNonQuery();

If a unique constraint violated during this insert how to avoid rollback the whole transaction , i want instead of that to skip the row which cause this unique constraint exception and resume the insert process .

You can modify your insert statement to make sure that you don't insert items that violate that constraint. — , Jun 12 '16 at 20:22
See this post: http://stackoverflow.com/questions/10703752/skip-over-ignore-duplicate-rows-on-insert. — ConnorsFan, Jun 12 '16 at 21:13
You might consider the [MERGE](https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_2030.htm) statement. The WHEN NOT MATCHED INSERT option might the one that's relevant to you. — Jonathan Leffler, Jun 13 '16 at 03:13
@JonathanLeffler Could you write my query in the question with your syntax please . — Anyname Donotcare, Jun 13 '16 at 09:57

score 1 · Accepted Answer · answered Jun 13 '16 at 17:57

It's a bit hard to guess what the schemas of the two tables in the question are, but we can make some guesses and test code against those guesses.

Semi-plausible schemas

CREATE TEMP TABLE sc1pen
(
    action_month   INTEGER NOT NULL,
    action_year    INTEGER NOT NULL,
    unidentified_1 INTEGER NOT NULL,
    emp_num        INTEGER NOT NULL,
    penalty_action INTEGER NOT NULL,
    penalty_string VARCHAR(30) NOT NULL,
    unidentified_2 INTEGER NOT NULL,
    unidentified_3 INTEGER NOT NULL
);

CREATE TEMP TABLE sc2pen
(
    action_month   INTEGER NOT NULL,
    action_year    INTEGER NOT NULL,
    unidentified_1 INTEGER NOT NULL,
    emp_num        INTEGER NOT NULL,
    penalty_action INTEGER NOT NULL,
    penalty_string VARCHAR(30) NOT NULL,
    penalty_reason VARCHAR(30) NOT NULL,
    unidentified_2 INTEGER NOT NULL,
    unidentified_3 INTEGER NOT NULL,
    sal_year       INTEGER NOT NULL,
    sal_month      INTEGER NOT NULL,
    penalty_type   INTEGER NOT NULL,
    pay_type       INTEGER NOT NULL
);

Translation of INSERT statement

This is a translation of the INSERT statement from the question.

INSERT INTO sc1pen 
    SELECT action_month, action_year, 200 AS unidentified_1, emp_num, penalty_action,
           'APPLY ' || penalty_reason || ' day ' AS penalty_string,
           0 AS unidentified_2, 0 AS unidentified_3
      FROM sc2pen
     WHERE sal_year = 2016 AND sal_month = 4 AND penalty_type = 1 AND pay_type = 0;

Since I was using a command-line tool and not embedded SQL or similar, I had to replace the question marks with values — 2016 for the sal_year and 4 for the sal_month.

Possible MERGE statement

MERGE INTO sc1pen AS dst
    USING (
        SELECT action_month, action_year, 200 AS unidentified_1, emp_num, penalty_action,
               'APPLY ' || penalty_reason || ' day ' AS penalty_string,
               0 AS unidentified_2, 0 AS unidentified_3
          FROM sc2pen
         WHERE sal_year = 2016 AND sal_month = 4 AND penalty_type = 1 AND pay_type = 0
    ) AS src
    ON dst.action_month = src.action_month AND
       dst.action_year = src.action_year AND
       dst.emp_num = src.emp_num
    WHEN NOT MATCHED THEN INSERT VALUES(src.action_month, src.action_year,
                src.unidentified_1, src.emp_num, src.penalty_action,
                src.penalty_string, src.unidentified_2, src.unidentified_3)
    ;

This is mostly 'read the manual' on the MERGE statement.

The ON clause seems semi-plausible; since there's no identification of the primary keys in the tables, we have to guess what the joining conditions should be.

How to skip unique constraint violation

1 Answers1

Semi-plausible schemas

Translation of INSERT statement

Possible MERGE statement