-
I think its better you read data of text file in DataSet
-
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
- Create XML from DataSet
- 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.