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

Obnoxio The Clown wrote:

> Ryan Hennessy wrote:
> >
> > I desperately need to reduce the number
> > of transactions to one.
>
> Why?

Because the operation for which I am trying to increase performance involves the creation of anywhere from 1000 to 8000 records in one go. The current implementation creates these records one-at-a-time, and the operation takes at least 20 minutes to complete on my Ultra 10. As the database interaction is the most significant factor, I need to get more work done per transaction to increase performance. Essentially, what I want to do is replace each of the 1000s of calls to db_add_mister_record(struct record*) with something like this:

char query[SOME_LARGE_SIZE] = "INSERT INTO record VALUES "; char query_pos = strlen(query);

/* LOOP A WHOLE PILE OF TIMES */ <pre processing>

/* previously we had db_add_mister_record(&temp_record) */

/* instead just append to the query */
query_pos += sprintf(query + query_pos, "(%d, %d, %d), ",

    temp_record.field_a, temp_record.field_b, temp_record.field_c);

/* END LOOPING */ /* overwrite trailing comma */
*strrchr(query, ',') = 0;

/* execute query */
db_execute_query(query);

But, obviously, the database gives me a syntax error for the multiple-row insert. Can anybody think of something clever that fits in with the above program structure?

Thanks,
Ryan. Received on Mon Jul 22 2002 - 17:34:05 CEST