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

Ryan Hennessy <ryan.hennessy_at_alcatel.com> wrote in message news:<3D3C1C44.3570B597_at_alcatel.com>...
> 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.

You could try to use a construction like

insert into A (a,b) values (x1,y1), (x2, y2), ..., (xn, yn)

where n is the number you want to insert at a time. I.e. something like (pseudocode)

String query = "insert into ... values "; while (more_params) {

	for (int i=0; i<N; i++) {
		if (! more_params)
			break;

		query += "(?, ?)";
  		if (i<N-1)
			query += ", ";
	}

	PreparedStatement ps = new PreparedStatement(query);
	for (int i=0; i<N; i++) {
		if (! more_params)
			break;

		addParameter(ps, fst_val);
		addParameter(ps, snd_val);
	}

	execute(ps);

commit();

Hope it helps
/Lennart Received on Mon Jul 22 2002 - 23:07:33 CEST