What is the fastest way to get a DataTable into SQL Server?

Question

I have a DataTable in memory that I need to dump straight into a SQL Server temp table.

After the data has been inserted, I transform it a little bit, and then insert a subset of those records into a permanent table.

The most time consuming part of this operation is getting the data into the temp table.

Now, I have to use temp tables, because more than one copy of this app is running at once, and I need a layer of isolation until the actual insert into the permanent table happens.

What is the fastest way to do a bulk insert from a C# DataTable into a SQL Temp Table?

I can't use any 3rd party tools for this, since I am transforming the data in memory.

My current method is to create a parameterized SqlCommand:

INSERT INTO #table (col1, col2, ... col200) VALUES (@col1, @col2, ... @col200)

and then for each row, clear and set the parameters and execute.

There has to be a more efficient way. I'm able to read and write the records on disk in a matter of seconds...

Answer 1

You should use the SqlBulkCopy class.

Answer 2

SqlBulkCopy will get the data in very fast.

I blogged not that long ago how to maximise performance. Some stats and examples in there. I compared 2 techniques, 1 using an SqlDataAdapter and 1 using SqlBulkCopy - bottom line was for bulk inserting 100K records, the data adapter approach took ~25 seconds compared to only ~0.8s for SqlBulkCopy.