In DB2 we can insert rows in two ways. One is insert single row with Values. The second one is multi row insert.
Store for Db2 books.
Arrays is the concept which was introduced in DB2 v8.
How to use arrays?
- Declare variable to hold no of rows
- Host variable declaration, write each variable in an array
- Move value to no of rows
- Insert statement give host variable array
01 HV-NUM-ROWS PIC S9(4) COMP. 01 HOST-VARIABLES. 05 HV-EMPNO-ARRAY PIC X(6) OCCURS 100 TIMES. 05 HV-LASTNAME-ARRAY OCCURS 100 TIMES. 49 HV-LASTNAME-LENGTH PIC S9(4) COMP. 49 HV-LASTNAME-TEXT PIC X(25). 05 HV-SALARY-ARRAY PIC S9(7)V9(2) OCCURS 100 TIMES. 05 HV-BONUS-ARRAY PIC S9(7)V9(2) OCCURS 100 TIMES. 05 HV-BONUS-NI-ARRAY PIC S9(4) COMP OCCURS 100 TIMES.
The program loads up the arrays with data to be inserted and then executes the following SQL statement:
MOVE +100 TO HV-NUM-ROWS. INSERT INTO EMP (EMPNO, LASTNAME, FIRSTNME, MIDINIT, SALARY, BONUS) VALUES (:HV-EMPNO-ARRAY, :HV-LASTNAME-ARRAY, :HV-SALARY-ARRAY, :HV-BONUS-ARRAY:HV-BONUS-NI-ARRAY) FOR :HV-NUM-ROWS ROWS ATOMIC
Atomic: Tells Db2 if an error occurs during insert, all the rows will be rolled back
NOT ATOMIC CONTINUE ON SQLEXCEPTION
It will continue insert all the rows,except erred rows.
To find rows which are not inserted, use SQL GET DIAGNOSTICS statement.
For Dynamic SQL, developers must specify For Multiple Rows during the PREPARE and For N Rows and Using for the list of variables in the EXECUTE. See the IBM manuals for further information.