Fast and simple way to import csv to SQL Server

Question

We are importing a csv file with CSVReader then using SqlBulkCopy to insert that data into SQL Server. This code works for us and is very simple, but wondering if there is a faster method (some of our files have 100000 rows) that would also not get too complex?

        SqlConnection conn = new SqlConnection(connectionString);
        conn.Open();
        SqlTransaction transaction = conn.BeginTransaction();
        try
        {
            using (TextReader reader = File.OpenText(sourceFileLocation))
            {
                CsvReader csv = new CsvReader(reader, true);
                SqlBulkCopy copy = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, transaction);
                copy.DestinationTableName = reportType.ToString();
                copy.WriteToServer(csv);
                transaction.Commit();
            }
        }
        catch (Exception ex)
        {
            transaction.Rollback();
            success = false;
            SendFileImportErrorEmail(Path.GetFileName(sourceFileLocation), ex.Message);
        }
        finally
        {
            conn.Close();
        }

Answer 1

Instead of building your own tool to do this, have a look at SQL Server Import and Export / SSIS. You can target flat files and SQL Server databases directly. The output dtsx package can also be run from the command line or as a job through the SQL Server Agent.

The reason I am suggesting it is because the wizard is optimized for parallelism and works really well on large flat files.

Answer 2

You should consider using a Table-Valued Parameter (TVP), which is based on a User-Defined Table Type (UDTT). This ability was introduced in SQL Server 2008 and allows you to define a strongly-typed structure that can be used to stream data into SQL Server (if done properly). An advantage of this approach over using SqlBulkCopy is that you can do more than a simple INSERT into a table; you can do any logic that you want (validate / upsert / etc) since the data arrives in the form of a Table Variable. You can deal with all of the import logic in a single Stored Procedure that can easily use local temporary tables if any of the data needs to be staged first. This makes it rather easy to isolate the process such that you can run multiple instances at the same time as long as you have a way to logically separate the rows being imported.

I posted a detailed answer on this topic here on S.O. a while ago, including example code and links to other info:

How can I insert 10 million records in the shortest time possible?

There is even a link to a related answer of mine that shows another variation on that theme. I have a third answer somewhere that shows a batched approach if you have millions of rows, which you don't, but as soon as I find that I will add the link here.