VB.net

Varsha B

12y2.6kVisual Basic .NET

hi,

In my datatable there are 1000 records, and i need to insert 500 first then in second loop 500 in database in VB.net

thanks

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Trivedi12y ago
hi,
using table value parameter, you can achieve this.
Please refer my article
http://www.c-sharpcorner.com/UploadFile/ff2f08/table-valued-parameter-in-sql-server-2008/
hope this will help you.
Varsha B12y ago
sqlParam = cmd.Parameters.AddWithValue("@CustomerList", dataTable)
sqlParam.SqlDbType = SqlDbType.Structured

@CustomerList is my UDT as Table thereby i'm consuming this table in my stored procedure to insert the records in to the table

Without creating XML how can i achieve this...?
Brijendra Gautam12y ago
SQL stores the xml into server memory, So if you try to insert very large xml at a time then it will impact the performance of SQL Query. Instead of sending all data at time, try to create xml for small bunch of data say 1000 records and then use the method suggested by Abhishek to insert it into the table.
Varsha B12y ago
My dataset contains 100000 records
So im inserting this all 100000 at a time, which is taking more time and going into deadlock situation,
So i need to loop the dataset and get 10000 records one after the other and insert into the database.

So how can i achieve this..?
Abhishek Jaiswal12y ago
heyy!

try this alternate way of inserting DATA in a faster way
http://www.sqlservercentral.com/blogs/nycnet/2012/05/11/one-way-to-insert-many-rows-very-fast-from-your-net-application/
:)
Brijendra Gautam12y ago
create the xml for first 500 records in first loop and pass it to a stored procedure. and then in next loop xml for next 500 records.
In Stored procedure,

read the xml and store it into a table variable or temp table and then use insert query to insert data into table.