What is the fastest way to get a DataTable into SQL Server?
- by John Gietzen
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...
