DB2- Multi Row Insert into a Table

Thanks to all readers of my blog.

Today I want to share info on how we can insert multi rows of data in a single pass.

EXEC SQL INSERT INTO test-table (col1, col2, col3) VALUES (:HV-ACT-NMBR-ARRAY, :HV-ACT-CITY-ARRAY, :HV-ACT-NAME-ARRAY ) FOR 50 ROWS ATOMIC END-EXEC.

In this example, the INSERT into table column is the same as any other INSERT statement you might have used in previous versions of DB2. Even the VALUES clause looks the same. However, in our example, the host variables used in the VALUES clause are all defined as arrays in our COBOL program. Adding FOR 50 ROWS tells DB2 that the arrays will contain 50 values.

And finally, the keyword ATOMIC tells DB2 to treat the entire INSERT as a single piece of work were all rows will be inserted or no rows will be inserted.

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.