Inserting multiple rows of data from host-variable arrays

Use host-variable arrays in your INSERT statement when you do not know at least some of the values to insert until the program runs.

About this task

You can use the FOR n ROWS form of the INSERT statement or the MERGE statement to insert multiple rows from values that are provided in host-variable arrays.

Each array contains values for a column of the target table. The first value in an array corresponds to the value for that column for the first inserted row, the second value in the array corresponds to the value for that column in the second inserted row, and so on. Db2 determines the attributes of the values based on the declaration of the array.

Example

Assume that the host-variable arrays HVA1, HVA2, and HVA3 have been declared and populated with the values that are to be inserted into the ACTNO, ACTKWD, and ACTDESC columns of the ACT table. The NUM-ROWS host variable specifies the number of rows to insert, which must be less than or equal to the dimension of each host-variable array.

You can insert the number of rows that are specified in the host variable NUM-ROWS by using the following INSERT statement:

EXEC SQL
  INSERT INTO DSN8C10.ACT 
    (ACTNO, ACTKWD, ACTDESC)
    VALUES (:HVA1, :HVA2, :HVA3)
    FOR :NUM-ROWS ROWS
END-EXEC.