This section describes the bulk INSERT.

EXEC SQL [ AT conn ] [ FOR { numOfRows | ARRAY_SIZE } ] 
	INSERT INTO tableName [ ( colName [, ...] ) ]
	{ VALUES ( { expr | DEFAULT } [, ...] ) [, ...] | query }
	[ RETURNING * | outputExpr [ [ AS ] outputName ] [, ...] 
	INTO outputHostVar [ [ INDICATOR ] indicatorVar  ]  [, ...] ];

Bulk INSERT is a feature that inserts multiple rows of data in bulk.

By specifying the array host variable that stored the data in the VALUES clause of the INSERT statement, the data for each element in the array can be inserted in bulk. This feature is used by specifying the insertion count in the FOR clause immediately before the INSERT statement.

FOR Clause

Specify the insertion count using numOfRows or ARRAY_SIZE in the FOR clause. The FOR clause can be specified only in the INSERT statement, not in other update statements.

numOfRows and ARRAY_SIZE

Insertion processing will be executed only for the specified count. However, if the count is 1, it will be assumed that the FOR clause was omitted when the application is executed. In this case, proceed according to the INSERT specification in the PostgreSQL Documentation.

Specify the FOR clause with a short, int, or long long host variable or with a literal.

Specify ARRAY_SIZE to insert all elements of the array in the table. When specifying ARRAY_SIZE, specify at least one array in expr.

If two or more arrays were specified in expr, it will be assumed that ARRAY_SIZE is the minimum number of elements in the array.

numOfRows or ARRAY_SIZE must exceed the minimum number of elements in all arrays specified in expr, outputHostVar, and indicatorVal.

The following example shows how to specify the FOR clause.

int  number_of_rows = 10;
int id[25];
char name[25][10]; 

EXEC SQL FOR :number_of_rows	/* will process 10 rows */
INSERT INTO prod (name, id) VALUES (:name, :id); 

EXEC SQL FOR ARRAY_SIZE   		/* will process 25 rows */ 
INSERT INTO prod (name, id) VALUES (:name, :id); 

expr

Specify the value to be inserted in the table. Array host variables, host variable literals, strings, and pointer variables can be specified. Structure type arrays and pointer variable arrays cannot be specified.

Do not use pointer variables and ARRAY_SIZE at the same time. The reason for this is that the number of elements in the area represented by the pointer variable cannot be determined.

query

A query (SELECT statement) that supplies the rows to be inserted. The number of rows returned by query must be 1. If two or more rows are returned, an error will occur. This cannot be used at the same time as ARRAY_SIZE.

outputHostVar, indicatorVal

These must be array host variables or pointer variables.

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"

Message

invalid statement name "Host array variable is needed when using FOR ARRAY_SIZE"

Message

SELECT...INTO returns too many rows

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.

• ECPG supports the use of 'WITH' clause in single INSERT statements. 'WITH' clause cannot be used in bulk INSERT statements.

• ECPG does not calculate the size of the pointer variable. So when a pointer variable is used that includes multiple elements, numOfRows should be less than or equal to the number of elements in the pointer. Otherwise, junk data will be inserted into the table.

• If an error occurs, all bulk INSERT actions will be rolled back, therefore, no rows are inserted. However, if the RETURNING clause was used, and the error occurred while obtaining the rows after the insertion was successful, the insertion processing will not be rolled back.

Given below are some sample usages of the bulk INSERT functionality.

Basic Bulk INSERT

int in_f1[4] = {1,2,3,4};
...
EXEC SQL FOR 3 INSERT INTO target (f1) VALUES (:in_f1);

The 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:

int num = 3;
int in_f1[4] = {1,2,3,4};
...
EXEC SQL FOR :num INSERT INTO target (f1) VALUES (:in_f1);

Inserting constant values

Constant values can also be bulk INSERTed into the table as follows:

EXEC SQL FOR 3 INSERT INTO target (f1,f2) VALUES (DEFAULT,'hello');

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)

Using ARRAY_SIZE

'FOR ARRAY_SIZE' can be used to insert the entire contents of a host array variable, without explicitly specifying the size, into the table.

	int in_f1[4] = {1,2,3,4};
...
EXEC SQL FOR ARRAY_SIZE INSERT INTO target (f1) VALUES (:in_f1);

In the above example, four rows are inserted into the table.

Note

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.

int in_f1[4] = {1,2,3,4};
char in_f3[3][10] = {"one", "two", "three"};
...
EXEC SQL FOR ARRAY_SIZE INSERT INTO target (f1,f3) VALUES (:in_f1,:in_f3);

In 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)

Using Pointers as Input

Pointers that contain multiple elements can be used in bulk INSERT.

int *in_pf1 = NULL;
in_pf1 = (int*)malloc(4*sizeof(int));
in_pf1[0]=1; 
in_pf1[1]=2; 
in_pf1[2]=3; 
in_pf1[3]=4; 
...
EXEC SQL FOR 4 INSERT INTO target (f1) values (:in_pf1);

The above example will insert four rows into the target table.

Using SELECT query

When using bulk INSERT, the input values can be got from the results of a SELECT statement. For example,

EXEC SQL FOR 4 INSERT INTO target(f1) SELECT age FROM source WHERE name LIKE 'foo';

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.

EXEC SQL FOR 1 INSERT INTO target(f1) SELECT age FROM source;

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'.

Using RETURNING clause

Bulk INSERT supports the same RETURNING clause syntax as normal INSERT. An example is given below.

int out_f1[4];
int in_f1[4] = {1,2,3,4};
...
EXEC SQL FOR 3 INSERT INTO target (f1) VALUES (:in_f1) RETURNING f1 INTO :out_f1;

After the execution of the above INSERT statement, the 'out_f1' array will have 3 elements with the values of '1','2' and '3'.