The blocked INSERT statement provides a means to insert multiple rows with a single SQLExecute request. For performance, it provides the one of the best ways 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:
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 blocked INSERT statement most efficiently performs inserts into a table when multiple records can be cached on the client and sent at once. The advantages with blocked INSERT are:
Example:
INSERT INTO TEST.TABLE1 ? ROWS VALUES (?, ?, ?, ?)
The INSERT statement has additional syntax that identifies it as a blocked INSERT. 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.