Use INSERT n ROWS

Applications that perform many INSERT statements in succession could be improved by using INSERT n ROWS. With this clause, you can insert one or more rows of data from a host structure array into a target table. This array must be an array of structures where the elements of the structure correspond to columns in the target table.

An SQL application that loops over an INSERT...VALUES statement (without the n ROWS clause) can be improved by using the INSERT n ROWS statement to insert multiple rows into the table. After the application has looped to fill the host array with rows, a single INSERT n ROWS statement inserts the entire array into the table. The statement runs faster because the SQL runtime was only called once and all the data was simultaneously inserted into the target table.

In the following table, the program attempted to INSERT 100 rows into a table. Note the differences in the number of calls to SQL runtime and to the database manager when blocking can be performed.

Table 1. Number of Calls Using an INSERT Statement
	Database Manager Not Using Blocking	Database Manager Using Blocking
Single-Row INSERT Statement	100 SQL runtime calls 100 database calls	100 SQL runtime calls one database call
Multiple-Row INSERT Statement	1 SQL runtime call 100 database calls	1 SQL runtime call 1 database call