Bulk insert from dataset into db?

[ganeshMohan]

Hi, I have a dataset,which is a combination of two tables and has more than 5000 records. I want to insert all rows from dataset into db using for loop for (int i=1;ds.tables[0].rows.count;i++) { insert into tbl1 insert into tbl2 } but it takes a long time how to decrease the time to insert? thanks in advance

Known is a drop , Unknown is an Ocean

[thatraja]

you can solve that by using SELECT Query with UNION ALL.

INSERT INTO tblDummy (column1, column2)
SELECT 'Value1' ,1
UNION ALL
SELECT 'Value2' ,2
UNION ALL
SELECT 'Value3' ,3
UNION ALL
SELECT 'Value4' ,4
UNION ALL
SELECT 'Value5' ,5

thatraja

[ganeshMohan]

thanks for the reply. but i know how to insert,i asked insert query takes more time. how to decrease the time? is it possible to insert with out looping? dataadapter.update() or dataset.acceptchanges() helpts to insert or update to databse but my scenario my dataset has data from two database tables [person,address] is any other way to insert?

Known is a drop , Unknown is an Ocean

[Abhishek Sur]

You can create bulk insert using OpenXML as DataSet is actually an XML. Send XML to your stored proc which will insert them to the database. See an excellent article regarding this : Bulk Insert from DataSet to Database[^] Use DataSet().GetXML() to get XML and pass it to the stored procedure

Abhishek Sur **Don't forget to click "Good Answer" if you like this Solution.

---

My Latest Articles-->****
InfoBox Visual Studio 2010 Extension
Windows7 API Code Pack
Simplify Code Using NDepend**

[thatraja]

your code will be like below.

string strQuery1="insert into tbl1(column) ";
string strQuery2="insert into tbl2(column2) ";

for (int i=1;ds.tables[0].rows.count;i++)
{
strQuery1 = strQuery1 + " SELECT " + ds.tables[0].Rows[0]["column1"].ToString() + " UNION ALL ";
strQuery2 = strQuery2 + " SELECT " + ds.tables[0].Rows[0]["column2"].ToString() + " UNION ALL ";
}

Now you get two queries in that two variables strQuery1, strQuery2, so you can execute with connection or command object. actually you are executing multiple insert query in loop so it takes long time, but in this way you will get single query so it takes less time. Then change the code depends on your no of columns which you want to store in db. also use stringbuilder instead of concatenating strings. thatraja

[T M Gray]

Have you looked at the SqlBulkCopy object?

[beginner in C net]

better u can use bulkcopy