VB.net

Question

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

Jignesh Trivedi · Answer

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 B · Answer

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 Gautam · Answer

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 B · Answer

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 Jaiswal · Answer

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 Gautam · Answer

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.