How best to insert 350,000 rows with ADO.Net

Question

I have a csv file with 350,000 rows, each row contains about 150 columns.

What would be the best way to insert these rows into SQL Server using ADO.Net?

As I usually did, you need to create the SQL statement manually. I was wondering if there is any way that I can encode it to just insert the whole datatable into SQL Server? Or a little short like that.

By the way, I already tried to do this using SSIS, but there are several data cleaning problems that I can deal with C #, but not so easily with SSIS. The data started as XML, but I changed it to CSV for simplicity.

Answer 1

Make a class "CsvDataReader" that implements IDataReader. Just implement Read (), GetValue (int i), Dispose () and the constructor: you can leave the rest by throwing a NotImplementedException if you want, because SqlBulkCopy will not call them. Use read to handle the reading of each row and GetValue to read the i-th value in the row.

Then pass it to SqlBulkCopy with the corresponding column mappings.

I get about 30,000 records per second with insertion speed using this method.

If you have control over the original file format, make it a tab delimiter, since it is easier to parse than CSV.

Edit: http://www.codeproject.com/KB/database/CsvReader.aspx - tx Mark Gravell.

Answer 2

SqlBulkCopy, if available. Here is a very useful explanation of using SqlBulkCopy in ADO.NET 2.0 with C #

I think you can load your XML directly into a DataSet and then map your SqlBulkCopy to a database and a DataSet.

Answer 3

Hey, you should go back to XML instead of csv, and then load this XML file into temp table using openxml, clear your data in temp table and then finally process this data.

I used this approach for huge data import, where my XML files are> 500 mb in size and openxml works like a charm.

You would be surprised at how much faster this will be compared to manual ado.net instructions.