C# - Inserting multiple rows using a stored procedure

Question

I have a list of objects, this list contains about 4 million objects. there is a stored proc that takes objects attributes as params , make some lookups and insert them into tables. what s the most efficient way to insert this 4 million objects to db? How i do : ``` -- connect to sql - SQLConnection ... foreach(var item in listofobjects) { SQLCommand sc = ... // assign params sc.ExecuteQuery(); } ``` THis has been really slow. is there a better way to do this? this process will be a scheduled task. i will run this ever hour, so i do expect high volume data like this.

Accepted Answer

Take a look at the SqlBulkCopy Class

based on your comment, dump the data into a staging table then do the lookup and insert into the real table set based from a proc....it will be much faster than row by row

C# - Inserting multiple rows using a stored procedure

Solution

Problem