Insert 2 million rows into SQL Server quickly

Question

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?

Answer 1

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(         connection,          SqlBulkCopyOptions.TableLock |          SqlBulkCopyOptions.FireTriggers |          SqlBulkCopyOptions.UseInternalTransaction,         null         );      // set the destination table name     bulkCopy.DestinationTableName = this.tableName;     connection.Open();      // write the data in the "dataTable"     bulkCopy.WriteToServer(dataTable);     connection.Close(); } // reset this.dataTable.Clear(); 

or

after doing step 1 at the top

2. Create XML from DataSet
3. 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.

Answer 2

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.