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

Multiple inner joins on same field or nested query?

Hi,

I'm wondering how I can structure an SQL statement to perform either a multiple join on a single table, or possibly using a sub-query. Basically, I've got one table in which both fields are foreign keys to another table, as follows:

Table #1

employee_id (pk)
employee_name

Table #2
teamleader_employee_id
backup_employee_id


both fields in table 2 need to do a lookup in table 1 to get the name of the actual employee. Do I need to use nested queries to accomplish this? Is it possible to do two inner joins on the same table? (I've tried this, unsuccessfully!) Any help is greatly appreciated!

-PJtwo joins to the same table, using table aliases to distinguish which table the rows are from, and column to distinguish the columns in the result set
select lead.employee_name as teamleader
, bkup.employee_name as backup
from table2
inner
join table1 as lead
on teamleader_employee_id = lead.employee_id
inner
join table1 as bkup
on backup_employee_id = bkup.employee_id|||Thank you very much. Works like a charm!

Multiple connection type query

Hi everyone,
Is it possible to perform a SELECT/INSERT statement with two different connection types? I want to do the "SELECT" statement with data from SQL Server and "INSERT" it into an Access database all in one query.
Sanctosuse linked tables to sql server in Access and create your insert query in access.