In a C# app, what is the most optimal way to insert many records into sql server?

Question

I need to perform a very large sql server insert from a c# application. Somewhere in the range of 20,000 through 50,000 records.

What is the fastest way through SQL server to perform the insert?

There are several options I know of, but I don't know which is the fastest.

   insert into MyTable(column1, column2, ..., column*)
   select 'value','value',...,'value'
   union
   select 'value','value',...,'value'

OR

   insert into MyTable(column1, column2, ..., column*)
   exec('select ''value'',''value'',...,''value'''
        'select ''value'',''value'',...,''value''')

OR

   bulk insert from a data file

OR

   Any better way that you know of :)

Answer 1

From C#, you can use SqlBulkCopy, but for raw speed overall, I expect you can get faster with bcp and you can really tune SSIS the most. In both those cases, you would effectively be using external executables - bcp or dtexec.

Answer 2

using SqlBulkCopy class is your best bet if you are going to use C# for bulk copying. I have used it to transfer 15K rows of data and it finishes off in a flash.

HTH