INSERTing data from a text file into SQL server (speed? method?)

Question

Got about a 400 MB .txt file here that is delimited by '|'. Using a Windows Form with C#, I'm inserting each row of the .txt file into a table in my SQL server database.

What I'm doing is simply this (shortened by "..." for brevity):

while ((line = file.ReadLine()) != null)
{
   string[] split = line.Split(new Char[] { '|' });

   SqlCommand cmd = new SqlCommand("INSERT INTO NEW_AnnualData VALUES (@YR1984, @YR1985, ..., @YR2012)", myconn);
   cmd.Parameters.AddWithValue("@YR1984", split[0]);
   cmd.Parameters.AddWithValue("@YR1985", split[1]);
   ...
   cmd.Parameters.AddWithValue("@YR2012", split[28]);

   cmd.ExecuteNonQuery();
}

Now, this is working, but it is taking awhile. This is my first time to do anything with a huge amount of data, so I need to make sure that A) I'm doing this in an efficient manner, and that B) my expectations aren't too high.

Using a SELECT COUNT() while the loop is going, I can watch the number go up and up over time. So I used a clock and some basic math to figure out the speed that things are working. In 60 seconds, there were 73881 inserts. That's 1231 inserts per second. The question is, is this an average speed, or am I getting poor performance? If the latter, what can I do to improve the performance?

I did read something about SSIS being efficient for this purpose exactly. However, I need this action to come from clicking a button in a Windows Form, not going through SISS.

Answer 1

Have a look at SqlBulkCopy on MSDN, or the nice blog post here. For me that goes up to tens of thousands of inserts per second.