ODBC support for multiple row statements

The multiple rowINSERT statement provides a means to insert multiple rows with a single SQLExecute request. From a performance standpoint, it provides the best way to populate a table, at times providing a tenfold performance improvement over the next best method.

The three forms of INSERT statements that can be executed from ODBC are:

• INSERT with VALUES using constants
• INSERT with VALUES using parameter markers
• multiple row INSERT

The INSERT with VALUES using constants statement is the least efficient method of performing inserts. For each request, a single INSERT statement is sent to the server where it is prepared, the underlying table is opened, and the record is written.

Example:

 
  INSERT INTO TEST.TABLE1 VALUES('ENGINEERING',10,'JONES','BOB')
 

The INSERT with VALUES using parameter markers statement performs better than the statement that uses constants. This form of the INSERT statement allows for the statement to be prepared only once and then reused on subsequent executions of the statement. It also allows the table on the server to remain open, thus removing the overhead of opening and closing the file for each insert.

Example:

 
  INSERT INTO TEST.TABLE1 VALUES (?, ?, ?, ?)
 

The multiple row INSERT statement most efficiently performs inserts into a table when multiple rows can be cached on the client and sent at once. The advantages with multiple row INSERT are:

• The data for multiple rows is sent in one communication request rather than one request per row.
• The server has an optimized path built into the database support for multiple row INSERT statements.

Example:

  INSERT INTO TEST.TABLE1 ? ROWS VALUES (?, ?, ?, ?)

The INSERT statement has additional syntax that identifies it as a multiple row INSERT. This optional syntax, the "? ROWS" clause, indicates that an additional parameter will be specified for this INSERT statement. It also indicates that the parameter will contain a row count that determines how many rows will be sent for that execution of the statement. The number of rows must be specified by means of the SQLSetStmtAttr API. Note that this extra clause is not needed; a multiple row INSERT statement can also run by preparing a INSERT with VALUES form of the statement with parameter markers, setting the row count on the SQLSetStmtAttr API, and then executing the statement.

To view examples of the APIs used from a C program for multiple row statements , see the multiple row insert and multiple row fetch C example topic.