Bulk insert records in the database (C# & SQL server)

A lot of times we come across a requirement to bulk insert data in SQL server. Now there are several ways to bulk insert data in SQL server table but in this example I’ll use SqlBulkCopy.

Why use SqlBulkCopy

  • SqlBulkCopy is super fast as compared to other data inserts.
  • You can specify different strategies for data insert & you can use multiple instances of SqlBulkCopy at the same time to work more efficiently.
  • you can use the Table lock option in SqlBulkCopy so that while updating it lock table instead of row (which is the default). This will massive performance gain while inserting a lot of records or loading data in a heap table. (Don’t use this option with clustered indexes)

In this example i have created a data table. I have added payload in datatable and SqlBulkCopy uses Writetoserver to insert this datatable in sqlserver

 DataTable dt = new DataTable("Products_Temp");
 dt = ConvertToDataTable(list); //object list is my dto
using (SqlConnection conn = new SqlConnection("your-connection-string"))
            {
                using (SqlCommand command = new SqlCommand("", conn))
                {
                    try
                    {
                        conn.Open();

                        //Bulk insert into temp table
                        using (SqlBulkCopy bulkcopy = new SqlBulkCopy(conn))
                        {
                            bulkcopy.BulkCopyTimeout = 660;
                            bulkcopy.DestinationTableName = "Products_Temp";
                            bulkcopy.WriteToServer(dt);
                            bulkcopy.Close();
                        }

                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine(ex.ToString());
                    }
                    finally
                    {
                        conn.Close();
                        Console.WriteLine(list.Count + " Records updated");
                    }
                }
            }

Conclusion

I’m using SqlBulkCoy for a long time and this class is one of my favorite classes. Although SqlBulkCopy normally uses to insert data, you can use it to do bulk updates as well. For example load data in the temp or staging table and after that run update statement to update the target table.

About the author

Naveed Ul-Haq

I'm Naveed. I am a UK based technical architect. I love working with .NET based CMS, eCommerce solutions, .NET Core, DevOps, and Cloud computing. I am a Certified Episerver CMS developer, MCSD (Microsoft Certified Solution Developer) and MCP in Azure application development. I spend my free time with my family and reading books. You can contact me on [email protected]om

View all posts

Leave a Reply

Your email address will not be published. Required fields are marked *