Multiple inserts

Hi Everyone -

I am trying to perform multiple inserts into a database -

At the moment, I have each insert on its own process -

But i would like to consolodate them into one statement...

Right now it is setup like this...

insert into trip_sample_table (trip_table_id, sample_speed) values (165,37);

is there a way to streamline this operation?

thanks
tonyHi Tony,

Sure. You can concatenate them together into a single round trip to the db, something like this:

insert into trip_sample_table (trip_table_id, sample_speed) values (165,37);insert into trip_sample_table (trip_table_id, sample_speed) values (165,37);insert into trip_sample_table (trip_table_id, sample_speed) values (165,37);insert into trip_sample_table (trip_table_id, sample_speed) values (165,37);insert into trip_sample_table (trip_table_id, sample_speed) values (165,37); and so on.

You can also read them from a temp table, for example, using a SELECT INTO, if you're running the code in a sproc.

How are you running the insert statements?

Don|||Hi Don -

that is exactly what i tried as well -

joining them all into a single insert with semi-colons to seperate
the statements -

and agin - this has caused a failure -
although the failure happeded a LOT faster... :-)

I'm beginning to think the web server itself is causing the
failure on the desktop program.

I'm using the SQLXML URL as the means to post the data -

but i'm leaning twords the updates are posting too fast for the server
to handle them...

Next week i'm going to go ahead and remove 2.0 of the SQLXML
and replace it with the SQLXML 3.0 and see if that solves the problem.

BTW -

I've also created a stored proc to handle the insert -
but the same thing is happening - using these methods

single line insert - one at a time
multiple line inserts - all at once, one long concatenated line -
single line insert using the stores proc

I've also put a thread.sleep(1000) between each
of the inserts, and although it runs MUCH slower -
it does get past the point of failure for the single line inserts.

I've also noticed the program just crashed out to the desktop -
even with a VERY large amount of try catches -

it just blows right out of the application.

thanks for responding.

take care
tony|||Hi Tony,

Yeah, SQLXML 2.0 (actually called something different: Web Release for SQL or something like that) had some issues. Make sure you install the SP1 of SQLXML 3.0. That version fixes a lot of stuff, and I'd say that's our prime suspect right now.

If it doesn't solve the problem, we can dig further. You hadn't mentioned you were using SQLXML, so there may be something about how you're using it that's causing a problem. Still kinda weird, though.

Keep us posted!
Don