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

Posted by John Gietzen on Stack Overflow See other posts from Stack Overflow or by John Gietzen
Published on 2010-06-11T15:19:38Z Indexed on 2010/06/11 15:22 UTC
Read the original article Hit count: 216

Filed under:
|
|
|
|

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...

© Stack Overflow or respective owner

Related posts about c#

Related posts about .NET