Best way to insert large amounts of data from a webform to SQL Server 2005

Hi

I have a VB.net web page which generates a datatable of values (3 columns and on average about 1000-3000 rows).

What is the best way to get this data table into an SQL Server? I can create a table on SQL Server no problem but I've found simply looping through the datatable and doing 1000-3000 insert statements is slow (a few seconds). I'd like to make this as streamlined as possible so was wondering is there is a native way to insert all records in a batch via ADO.net or something.

Any ideas?
Thanks
Ed

Hi,

If you gonna insert or update in single table then use SQL Data adapter. But it needs to have primary or unique key to create update query via sql command builder otherwise its just insert all rows in your table.

If you want to update for above scenario there are two ways.

1. Create staging table in your database and insert your records using sqlbulkcopy class which can insert lakhs of data/s (i'm not sure abt amt), then create a stored procedure to update or insert rows from staging table to main table

2. You can write your update and insert command in sqldataadapter.sqlcommand property.

for example

create a table as testtable with id number and name as text

Sql query will be like this

update testtable
set name = @.namecolumn
Where id = @.idColumn
If @.@.Rowcount = 0
Insert into testtable (id,name) values(@.idColumn,@.nameColumn)

then add sql parameters(@.idcolumn,@.nameColumn) with your data table column names

then adapter will execute the query while you call update method

hope it helps