Hi,

I want to insert multiple records (between 5000 -10000) from one table to
another. I find however, it takes a bit of time due to the amount of records
(this is a web application so time is of the essence).

Is there a way to allow for batch inserts (ie, 10-20 at a time) which would
speed this up?

Khurram

Khurram,

my understanding is that web application constructs on the fly sql
statements and you are looking the optimal format for the statement. If that
is correct assumption, here is a few approaches:

consider constructing a number of select statements combined by union
insert into t ( a, b, c )
select 1, 2, 3 union
select 4, 5, 6 union
....
select x, y, z -- final select

or. pass inserts as separate statements in the same sql batch
insert into t ( a, b, c ) select 1, 2, 3 union
go
insert into t ( a, b, c ) select 4, 5, 6 union
go
....
insert into t ( a, b, c ) select x, y, z union
go

you can pass this way a few megabytes of code at once.

alternate approach is not advised if you need a level of security, here is
the idea, drop the data into comma delimited format file on the web side
with well known name
then execute BULK INSERT statement on sql server pointing to this file -
this would be possibly the fastest way to "insert" values, but it will take
a bit of logistics on the web application side such as saving the file with
data, combing data so that delimiter was placed appropriately.

-- cheers
Miron.

[quoted text, click to view]