Bulk INSERT can be used to input multiple rows of data into the table using a single ECOBPG statement that uses the newly introduced 'FOR' clause.
This functionality allows the user to make use of the data stored in host array variables, resulting in 'COBOL' client programs that are simpler and easier to maintain.
In sample codes, declarations of section and division, line numbers and/or indents may be omitted.
Synopsis
The syntax of the bulk INSERT statement is given below:
EXEC SQL [ AT connection ] [ FOR {number_of_rows|ARRAY_SIZE}] INSERT INTO table_name [ ( column_name [, ...] ) ] { VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query } [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] INTO output_host_var [ [ INDICATOR ] indicator_var ] [, ...]] END-EXEC
When the above bulk INSERT command is used, ECOBPG inserts 'number_of_rows' number of rows into the table, using the data that is stored in the 'expression'.
The 'FOR' clause indicates to ECOBPG that the given INSERT statement is a bulk insert statement. The 'FOR' clause currently only support INSERT statements. When a 'FOR' clause specified, ECOBPG executes the INSERT statement for 'number_of_rows' number of times, iterating through the host array variables.
The 'FOR' clause can iterate through all the array elements or can be limited to process only a fixed number of array elements. The value for 'number_of_rows' should be greater than zero.
The 'FOR' clause can accept an integer host variable(COMP or COMP-5) or an integer literal as its parameter. It can also accept the constant 'ARRAY_SIZE' in which case all the elements in the host array variable are inserted into the table.
Given below are examples of acceptable bulk INSERT statements.
01 NUMBER-OF-ROWS PIC S9(9) COMP VALUE 10.
01 GROUP-ITEM.
05 ID1 PIC S9(9) OCCURS 25.
05 NAME PIC X(10) OCCURS 25.
* will process 10 rows
EXEC SQL FOR :NUMBER-OF-ROWS
INSERT INTO prod (name, id) VALUES (:NAME, :ID1) END-EXEC
* will process 25 rows
EXEC SQL FOR ARRAY_SIZE
INSERT INTO prod (name, id) VALUES (:NAME, :ID1) END-EXECWhen 'FOR' clause is specified, the 'expression' can be a host array variable, host variable, constant number or string.
The value given for the 'number_of_rows' should be greater than zero. Value which is less than or equal to zero will result in a run-time error.
When 'FOR ARRAY_SIZE' is specified, the values clause should consist of at least one host array variable. 'SELECT' queries cannot be used for input values when using 'FOR ARRAY_SIZE'.
When the value given for the 'number_of_rows' is greater than one, the specified 'SELECT' query should returns only one row. More than one returned row will result in an error.
The VALUES clause includes the input data that is to be inserted into the table. When working with FOR clause, the values in the 'expression' can be host array variables, host variable, constant number, string or pointers.
See
For more detailed usage of the INSERT statement, please refer to the ECOBPG section of the PostgreSQL documentation.
Error Messages
Given below are the error messages that are output when bulk INSERT functionality is not used correctly.
invalid statement name "FOR value should be positive integer"
The value given for number_of_rows is less than or equal to 0.
Specify a value that is more than or equal to 1 for number_of_rows.
invalid statement name "Host array variable is needed when using FOR ARRAY_SIZE"
A host array is not specified in the values clause when using the ARRAY_SIZE keyword.
At least one host array variable should be included in the values clause
SELECT...INTO returns too many rows
The number of rows returned by the 'SELECT ... INTO' query in the INSERT statement is more than one.
When the value of 'number_of_rows' is more than one, the maximum number of rows that can be returned by the 'SELECT ... INTO' query in the INSERT statement is one.
Limitations
The limitations when using bulk INSERT are given below.
Array of structures should not be used as an input in the 'VALUES' clause. Attempted use will result in junk data being inserted into the table.
Array of pointers should not be used as an input in the 'VALUES' clause. Attempted use will result in junk data being inserted into the table.
ECOBPG supports the use of 'WITH' clause in single INSERT statements. 'WITH' clause cannot be used in bulk INSERT statements.
Samples
Given below are some sample usages of the bulk INSERT functionality.
01 GROUP-ITEM.
05 IN-F1 PIC S9(9) OCCURS 4.
MOVE 1 TO IN-F1(1)
MOVE 2 TO IN-F1(2)
MOVE 3 TO IN-F1(3)
MOVE 4 TO IN-F1(4)
...
EXEC SQL FOR 3 INSERT INTO target (f1) VALUES (:IN-F1) END-EXECThe number of rows to insert indicated by the FOR clause is 3, so the data in the first 3 elements of the host array variable are inserted into the table. The contents of the target table will be:
f1 ---- 1 2 3 (3 rows)
Also a host integer variable can be used to indicate the number of rows that will be inserted in FOR clause, which will produce the same result as above:
01 NUM PIC S9(9) COMP VALUE 3.
01 GROUP-ITEM.
05 IN-F1 PIC S9(9) OCCURS 4.
MOVE 1 TO IN-F1(1)
MOVE 2 TO IN-F1(2)
MOVE 3 TO IN-F1(3)
MOVE 4 TO IN-F1(4)
...
EXEC SQL FOR :NUM INSERT INTO target (f1) VALUES (:IN-F1) END-EXECConstant values can also be bulk INSERTed into the table as follows:
EXEC SQL FOR 3 INSERT INTO target (f1,f2) VALUES (DEFAULT,'hello') END-EXEC
Assuming the 'DEFAULT' value for the 'f1' column is '0', the contents of the target table will be:
f1 | f2 ---+------- 0 | hello 0 | hello 0 | hello (3 rows)
'FOR ARRAY_SIZE' can be used to insert the entire contents of a host array variable, without explicitly specifying the size, into the table.
01 GROUP-ITEM.
05 IN-F1 PIC S9(9) OCCURS 4.
MOVE 1 TO IN-F1(1)
MOVE 2 TO IN-F1(2)
MOVE 3 TO IN-F1(3)
MOVE 4 TO IN-F1(4)
...
EXEC SQL FOR ARRAY_SIZE INSERT INTO target (f1) VALUES (:IN-F1) END-EXECNote
If there are multiple host array variables specified as input values, then the number of rows inserted is same as the smallest array size. The example given below demonstrates this usage.
01 GROUP-ITEM.
05 IN-F1 PIC S9(9) OCCURS 4.
05 IN-F3 PIC X(10) OCCURS 3.
MOVE 1 TO IN-F1(1)
MOVE 2 TO IN-F1(2)
MOVE 3 TO IN-F1(3)
MOVE 4 TO IN-F1(4)
MOVE "one" TO IN-F3(1)
MOVE "two" TO IN-F3(2)
MOVE "three" TO IN-F3(3)
...
EXEC SQL FOR ARRAY_SIZE INSERT INTO target (f1,f3) VALUES (:IN-F1,:IN-F3) END-EXECIn the above example, the array sizes are 3 and 4. Given that the smallest array size is 3, only three rows are inserted into the table. The table contents are given below.
f1 | f3 ----+------ 1 | one 2 | two 3 | three (3 rows)
When using bulk INSERT, the input values can be got from the results of a SELECT statement. For ex.,
EXEC SQL FOR 4 INSERT INTO target(f1) SELECT age FROM source WHERE name LIKE 'foo' END-EXEC
Assuming that the 'SELECT' query returns one row, the same row will be inserted into the target table four times.
Note
If the 'SELECT' query returns more than one row, the INSERT statement will throw an error.
If the 'number_of_rows' is '1' and the 'SELECT' query returns more than one row, then all the rows are inserted into the table.
EXEC SQL FOR 1 INSERT INTO target(f1) SELECT age FROM source END-EXEC
In the above example, all the rows returned by the 'SELECT' statement will be inserted into the table. In this context '1' has the meaning of 'returned row equivalent'.
Bulk INSERT supports the same RETURNING clause syntax as normal INSERT. An example is given below.
01 GROUP-ITEM.
05 IN-F1 PIC S9(9) OCCURS 4.
05 OUT-F1 PIC S9(9) OCCURS 4.
MOVE 1 TO IN-F1(1)
MOVE 2 TO IN-F1(2)
MOVE 3 TO IN-F1(3)
MOVE 4 TO IN-F1(4)
...
EXEC SQL FOR 3 INSERT INTO target (f1) VALUES (:IN-F1) RETURNING f1 INTO :OUT-F1 END-EXECAfter the execution of the above INSERT statement, the 'out_f1' array will have 3 elements with the values of '1','2' and '3'.