Data Manipulation with SQL › Bulk Processing › Executing a Bulk Insert

Executing a Bulk Insert

A bulk insert is an INSERT statement that adds multiple rows in a host variable array to the database.

To execute a bulk insert:

1. Declare a host variable array
2. Assign values to the host variable array
3. Issue the INSERT statement with the BULK clause

Specifying the START and ROWS Parameters

A bulk insert adds as many rows from the host variable array as are specified in the ROWS parameter, starting from the row specified in the START parameter. If START and ROWS are not specified, these are the defaults:

• The starting row is the first entry in the array
• The number of rows inserted is the number of occurrences defined for the array

Note: If the array is not full, specify a ROWS parameter value equal to the number of occurrences in the array that contain data. This ensures that the DBMS will not attempt to insert array occurrences that contain no data.

Bulk Insert Example

In this example, the program declares a host-variable array with an INCLUDE TABLE statement. After values are assigned to the array, the program issues a statement to insert all of the data in the array:

 EXEC SQL

    INCLUDE TABLE SKILL AS BULK-SKILL
      NUMBER OF ROWS 100
      PREFIX 'BULK-'
 END-EXEC.
  .
  .
  .
 (Assign values to BULK-SKILL array)
  .
  .
  .
 EXEC SQL
    INSERT INTO SKILL
      BULK :BULK-SKILL
        ROWS :NUM-ROWS
 END-EXEC.

 IF SQLCODE < 0
   MOVE SQLCNRP TO FAILING-ROW-NUM
   PERFORM ERROR-ROUTINE.

Checking Bulk Insert Status

To detect unsuccessful execution of a bulk insert, test for SQLCODE < 0.

If the result of the test is true, the value of SQLCNRP equals the relative row number (from the specified starting row) of the row which caused the failure. The DBMS rolls back the results of the failing row but not the results of the prior rows.

The following table shows the possible combinations of SQLCODE and SQLCNRP values on a bulk insert:

Advantage of a Bulk Insert

A bulk insert adds a group of rows using fewer resources than if the program issues a separate INSERT statement for each row.