Insert 2 million rows into SQL Server quickly


I have to insert about 2 million rows from a text file.

And with inserting I have to create some master tables.

What is the best and fast way to insert such a large set of data into SQL Server?

This question is tagged with c# sql-server database

~ Asked on 2012-12-05 11:14:24

The Best Answer is


You can try with SqlBulkCopy class.

Lets you efficiently bulk load a SQL Server table with data from another source.

There is a cool blog post about how you can use it.

~ Answered on 2012-12-05 11:23:35


  1. I think its better you read data of text file in DataSet

  2. Try out SqlBulkCopy - Bulk Insert into SQL from C# App

    // connect to SQL
    using (SqlConnection connection = new SqlConnection(connString))
        // make sure to enable triggers
        // more on triggers in next post
        SqlBulkCopy bulkCopy = new SqlBulkCopy(
            SqlBulkCopyOptions.TableLock | 
            SqlBulkCopyOptions.FireTriggers | 
        // set the destination table name
        bulkCopy.DestinationTableName = this.tableName;
        // write the data in the "dataTable"
    // reset


after doing step 1 at the top

  1. Create XML from DataSet
  2. Pass XML to database and do bulk insert

you can check this article for detail : Bulk Insertion of Data Using C# DataTable and SQL server OpenXML function

But its not tested with 2 million record, it will do but consume memory on machine as you have to load 2 million record and insert it.

~ Answered on 2012-12-05 11:18:12

Most Viewed Questions: