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

From: Alex Petrov <master.db_at_mail.ru>
Date: Tue, 23 Jul 2002 01:33:53 +0000 (UTC)
Message-ID: <28fbfb9469fc21e376305cb9e1b66aae.54296_at_mygate.mailgate.org>


Hi,

Have you considered of bulk data copy/load directly to the database? If you are using a commercial DBMS (like Oracle, Sybase or Informix on the Unix), as I know all of them support bulk copy/load operations, and C/C++ applications can utilize them using either DBMS' native API or ODBC API.
Bulk copy/load architecture is specially intented to provide the best perofrmance for massive data loads.
Also, if you want, bulk operations can be performed without "marking" the transaction/change log, ie. without opening a transaction.

Hope this helps...

Alex Petrov
Sys analyst,mcdba,ocp

~~~~~~~~~ ~~~~~~~~~ ~~~~~~~~~ ~~~~~~~~~ ~~~~~~~~~ From: Ryan Hennessy <ryan.hennessy_at_alcatel.com> Subject: How can I insert multiple rows in a single SQL statement Date: Mon, 22 Jul 2002 10:52:52 -0400
Organization: Alcatel Canada

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.

-- 
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
Received on Tue Jul 23 2002 - 03:33:53 CEST

Original text of this message