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 a form of the INSERT statement or 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 the column in the second inserted row, and so on. DB2® determines the attributes of the values based on the declaration of the array.

Example

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 DSN8A10.ACT 
    (ACTNO, ACTKWD, ACTDESC)
    VALUES (:HVA1, :HVA2, :HVA3)
    FOR :NUM-ROWS ROWS
END-EXEC.

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. The NUM-ROWS host variable specifies the number of rows that are to be inserted, which must be less than or equal to the dimension of each host variable array.

Parent topic: Using host variable arrays in SQL statements