Re: How can I insert multiple rows in a single SQL statement

Fastest way from an application is an insert cursor with a maxed out insert buffer. See the code for my dbcopy.ec or ul.ec utilities for how to do this. It involves the FET_BUF_SIZ environment var or the equivalent global variable in your code to adjust the communication buffer size from the default of 4K to the max of 32767 bytes, declaring a CURSOR for the insert statement, then PUTting to the cursor over and over again, perhaps manually flushing and committing the cursor every few 1000 rows and handling errors and possible recovery. All of this is demonstrated in dbcopy complete with the sometimes complex error recovery required whenusing PUT cursors.

Dbcopy and ul are part of the package utils2_ak available from the IIUG Software Repository.

The other suggestion someone made, to write out a delimited file, was also not bad. As to loading the file, see the Informix dbload utility which is much faster than the dbaccess LOAD verb and handles partial commits like dbcopy does (as suggested above).

Art S. Kagel

Ryan Hennessy wrote:

> Good morning,
>
> I'm having trouble coming up with a way to insert multiple rows worth of
> arbitrary values into a table in a single SQL query. The values are
> coming from the C layer overtop the database, as opposed to coming from
> a select on another table. My only tool here is sprintf, which I can
> use to incrementally build the query string.
>
> I am disappointed that insert into will only take a single row of
> values, and that similarly, a stored procedure that inserts a row of
> values can only be called once. I desperately need to reduce the number
> of transactions to one. Somebody has made the suggestion that I could
> select my values into a temporary table, and then use insert on that,
> but I am unsure if this is the best method available to me. I can't
> make a stored procedure to insert multiple rows at once, because I can't
> make the stored procedure take a variadic number of arguments, and the
> values are too random for simple loops to work.
>
> Can somebody think of anything clever?
>
> Thanks,
> Ryan.
>
>
Received on Wed Jul 24 2002 - 00:13:31 CEST