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

Question

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

After the data has been inserted, I will transform it a bit, and then paste a subset of these records into a persistent table.

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

Now I have to use temporary tables, because several copies of this application are launched immediately, and I need an isolation layer until the actual insertion into the constant table occurs.

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

I can not use third-party tools for this, as I convert data to memory.

My current method is to create a parameterized SqlCommand:

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

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

There should be a more efficient way. I can read and write to disk in seconds ...

Answer 1

You must use the SqlBulkCopy class.

Answer 2

SqlBulkCopy will receive data very quickly.

I did not write so long ago how to maximize performance. Some statistics and examples are there. I compared 2 methods, 1 using SqlDataAdapter and 1 using SqlBulkCopy. The bottom line was for mass input of 100K entries, the approach to the data adapter took ~ 25 seconds compared to ~ 0.8 s for SqlBulkCopy.