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

User · Answer

I ran into this scenario recently  well over 7 million rows  and eneded up using sqlcmd via powershell  after parsing raw data into SQL insert statements  in segments of 5 000 at a time  SQL can t handle 7 million lines in one lump job or even 500 000 lines for that matter unless its broken down into smaller 5K pieces   You can then run each 5K script one after the other   as I needed to leverage the new sequence command in SQL Server 2012 Enterprise   I couldn t find a programatic way to insert seven million rows of data quickly and efficiently with said sequence command   Secondly  one of the things to look out for when inserting a million rows or more of data in one sitting is the CPU and memory consumption  mostly memory  during the insert process   SQL will eat up memory CPU with a job of this magnitude without releasing said processes   Needless to say if you don t have enough processing power or memory on your server you can crash it pretty easily in a short time  which I found out the hard way    If you get to the point to where your memory consumption is over 70-75  just reboot the server and the processes will be released back to normal     I had to run a bunch of trial and error tests to see what the limits for my server was  given the limited CPU Memory resources to work with  before I could actually have a final execution plan   I would suggest you do the same in a test environment before rolling this out into production

User · Answer

I use the bcp utility    Bulk Copy Program  I load about 1 5 million text records each month  Each text record is 800 characters wide  On my server  it takes about 30 seconds to add the 1 5 million text records into a SQL Server table   The instructions for bcp are at http   msdn microsoft com en-us library ms162802 aspx

User · Answer

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

User · Answer

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

User · Answer

Re the solution for SqlBulkCopy   I used the StreamReader to convert and process the text file  The result was a list of my object    I created a class than takes Datatable or a List lt T gt  and a Buffer size  CommitBatchSize   It will convert the list to a data table using an extension  in the second class    It works very fast  On my PC  I am able to insert more than 10 million complicated records in less than 10 seconds   Here is the class   using System  using System Collections  using System Collections Generic  using System ComponentModel  using System Data  using System Data SqlClient  using System Linq  using System Text  using System Threading Tasks   namespace DAL    public class BulkUploadToSql lt T gt        public IList lt T gt  InternalStore   get  set        public string TableName   get  set        public int CommitBatchSize   get  set    1000      public string ConnectionString   get  set         public void Commit                 if  InternalStore Count gt 0                        DataTable dt              int numberOfPages    InternalStore Count   CommitBatchSize      InternalStore Count   CommitBatchSize    0   0   1               for  int pageIndex   0  pageIndex  lt  numberOfPages  pageIndex                                          dt  InternalStore Skip pageIndex   CommitBatchSize  Take CommitBatchSize  ToDataTable                    BulkInsert dt                                           public void BulkInsert DataTable dt                using  SqlConnection connection   new SqlConnection ConnectionString                            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   TableName              connection Open                    write the data in the  dataTable              bulkCopy WriteToServer dt               connection Close                         reset           this dataTable Clear              public static class BulkUploadToSqlHelper       public static DataTable ToDataTable lt T gt  this IEnumerable lt T gt  data                PropertyDescriptorCollection properties               TypeDescriptor GetProperties typeof T            DataTable table   new DataTable            foreach  PropertyDescriptor prop in properties              table Columns Add prop Name  Nullable GetUnderlyingType prop PropertyType     prop PropertyType           foreach  T item in data                        DataRow row   table NewRow                foreach  PropertyDescriptor prop in properties                  row prop Name    prop GetValue item     DBNull Value              table Rows Add row                     return table               Here is an example when I want to insert a List of my custom object List lt PuckDetection gt   ListDetections    var objBulk   new BulkUploadToSql lt PuckDetection gt              InternalStore   ListDetections          TableName   PuckDetections           CommitBatchSize 1000          ConnectionString  ENTER YOU CONNECTION STRING     objBulk Commit      The BulkInsert class can be modified to add column mapping if required  Example you have an Identity key as first column  this assuming that the column names in the datatable are the same as the database     ADD COLUMN MAPPING foreach  DataColumn col in dt Columns            bulkCopy ColumnMappings Add col ColumnName  col ColumnName

User · Answer

I tried with this method and it significantly reduced my database insert execution time   List lt string gt  toinsert   new List lt string gt     StringBuilder insertCmd   new StringBuilder  INSERT INTO tabblename  col1  col2  col3  VALUES      foreach  var row in rows             the point here is to keep values quoted and avoid SQL injection       var first   row First Replace                  var second   row Second Replace                  var third   row Third Replace                   toinsert Add string Format      0      1      2       first  second  third      if  toinsert Count    0          insertCmd Append string Join      toinsert          insertCmd Append         using  MySqlCommand myCmd   new MySqlCommand insertCmd ToString    SQLconnectionObject           myCmd CommandType   CommandType Text        myCmd ExecuteNonQuery         Create SQL connection object and replace it where I have written SQLconnectionObject

[c#] Insert 2 million rows into SQL Server quickly

Examples related to c#

Examples related to asp.net

Examples related to sql-server

Examples related to database

Examples related to ado.net