What is the purpose and usage of ATOMIC and NON-ATOMIC clause in multi row insert?
The ATOMIC and NON ATOMIC clauses are used with the multi-row insert. ATOMIC is always processed by default if any of the options is not given. The ATOMIC clause states that if there is a failure while inserting any one row during multi-row insertion then the entire query will be failed and all the inserts will be rolled back.
The NON ATOMIC clause is just the opposite of the ATOMIC clause. It is used when we have to insert and process all the rows individually in a multi-row insert. For example, this option can be used like below.
MOVE 50 TO MAX-ROWS
EXEC SQL
INSERT INTO ORDERS (ORDER_ID, ORDER_DATE)
VALUES(:ORDER-ID, :ORDER-DATE) FOR
:MAX-ROWS ROWS NON ATOMIC
END-EXEC
Published on 15-Sep-2020 14:33:23
