SQL data upload

Question

I was wondering if there is a way to upload data from my ASP.NET app to sql server in pages? I'm trying to upload 5000 records to sql server and if I try to do them all at once it crashes and wanted to do it in pages of 100 or so at a time if that is possible.

Answer 1

Hi @austin branham

You can use SqlBulkCopy, even you can insert more 10k records within few seconds. Check below link with examples. Even you can use batch.

  string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;  
        using (SqlConnection con = new SqlConnection(consString))  
        {  
            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))  
            {  
                //Set the database table name  
                sqlBulkCopy.DestinationTableName = "dbo.Customers";  
                // Set the BatchSize.  
                sqlBulkCopy.BatchSize = 100;  
   
                //[OPTIONAL]: Map the DataTable columns with that of the database table  
                sqlBulkCopy.ColumnMappings.Add("Id", "CustomerId");  
                sqlBulkCopy.ColumnMappings.Add("Name", "Name");  
                sqlBulkCopy.ColumnMappings.Add("Country", "Country");  
                con.Open();  
                sqlBulkCopy.WriteToServer(dt);  
                con.Close();  
            }  
        }  

https://stackoverflow.com/questions/13722014/insert-2-million-rows-into-sql-server-quickly

https://visualstudiomagazine.com/articles/2013/03/01/effective-use-of-sql-bulk-insert.aspx

Answer 2

What you intend to do we call it batching. Please find here documentation of how to implement it and the performance gains on specific scenarions. With Azure SQL, batching is very recommended.