If we want to insert a multiple rows in a DB2 table using a single INSERT command then we have to define the host variable array and move the row data we want to insert in that array. We need to define another variable in the working storage section with configuration S9(4) COMP which will hold the number of rows to be inserted. We can insert the multiple rows as below−
MOVE 50 TO MAX-ROWS EXEC SQL INSERT INTO ORDERS (ORDER_ID, ORDER_DATE) VALUES(:ORDER-ID, :ORDER-DATE) FOR :MAX-ROWS ROWS END-EXEC
The ORDER-ID and ORDER-DATE are host variables which should be defined as an array and all the data should be populated in these variables before the INSERT statement.