Different ways of Handling Bulk Inserts using C# and SQL Server

 When we need to insert bulk data into sql database through application we have different options to do so. Bulk data can be like 10k records or 100k records or even more. We need to keep the amount of time it takes to insert data as minimal as possible. Otherwise it will hit the performance of the application. The below are the different ways of doing the bulk inserts.

1) SqlBulkCopy (Available from ASP.Net 2.0)
2) Table Valued Parameters (Available from SQL Server 2008+)
3) Short Hand Insert Query


Execution time Comparison with 50K Records Insertion:

1) SqlBulkCopy: It is a class which enables you to send bulk data to Sql Server table. You can send the data using DataTable. You can use WriteToServer() method to make a request to sql server for bulkinsert.

While the bulk copy operation is in progress, the associated destination SqlConnection is busy serving it, and no other operations can be performed on the connection.

Sample Code:

In this example I am using Employees table which has three columns called EmpName, Age, Salary.

Table:

CREATE TABLE [dbo].[Employees](  

    [Id] [int] IDENTITY(1,1) NOT NULL,  

    [EmpName] [nvarchar](50) NULL,  

    [Age] [real] NULL,  

    [Salary] [money] NULL,  

 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED   

(  

    [Id] ASC  

)) ON [PRIMARY]  

C# Code:

Step#1: Define a connection string:

1. string consString = "Data Source =Server Name; Initial Catalog = Database Name; User Id = User Name; Password = Password;";  

Step #2: Construct DataTable which contains data to send. In this example we are using 50k records to send.

public DataTable GetDataToInsert()  

{  

            DataTable dt = new DataTable();  

            dt.Columns.Add("EmpName", typeof(string));  

            dt.Columns.Add("Age", typeof(decimal));  

            dt.Columns.Add("Salary", typeof(decimal));  

  

            for (int i = 0; i < 50000; i++)  

            {  

                dt.Rows.Add("DemoEmployee" + i, 35, 50000 + i);  

            }  

  

            return dt;  

 }  

Step #3: Make a call to Sql Server using SqlBulkCopy class.

public void InsertDataWithBulkCopy()  

 {  

            using (SqlConnection con = new SqlConnection(consString))  

            {  

                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(con))  

                {  

                    con.Open();  

                    bulkCopy.DestinationTableName = "dbo.Employees";  

                    bulkCopy.BatchSize = 10000;  

                    try  

                    {  

                        // Write from the source to the destination.  

                        bulkCopy.WriteToServer(GetDataToInsert());  

                    }  

                    catch (Exception ex)  

                    {  

                        Console.WriteLine(ex.Message);  

                    }  

                    bulkCopy.Close();  

                    con.Close();  

                }  

            }  

}  

In the above example we have set DestinationTableName as Employees because that is table in which we want to insert our data.

2) Table Valued Parameters:
 Using table valued parameters is another way of handling bulk insertions. But the only drawback or prerequisite is that we need to create a Table Valued type in order to use table valued parameters.  You can create Table Valued Type by using the below syntax.

Note: You cannot return data in a table-valued parameter. Table-valued parameters are input-only; the OUTPUT keyword is not supported.

CREATE TYPE [dbo].[Tvp_Employees] AS TABLE(  

    [EmpName] [nvarchar](50) NULL,  

    [Age] [real] NULL,  

    [Salary] [money] NULL  

)  

C# Code:

public void InsertDataWithTVP()  

        {  

            using (SqlConnection con = new SqlConnection(consString))  

            {  

                var sqlQuery = @"INSERT INTO dbo.Employees(EmpName,Age, Salary) SELECT emp.EmpName, emp.Age,emp.Salary FROM @tvpEmployees AS emp";  

                using (SqlCommand cmd = new SqlCommand(sqlQuery, con))  

                {  

                    SqlParameter tvpParam = cmd.Parameters.AddWithValue("@tvpEmployees", GetDataToInsert());  

                    tvpParam.SqlDbType = SqlDbType.Structured;  

                    tvpParam.TypeName = "dbo.Tvp_Employees";  

                    con.Open();  

                    cmd.ExecuteNonQuery();  

                    con.Close();  

                }  

            }  

        }  

3) Short Hand Insert Query:
We use short hand insert query to handle bulk data. But it has a limitation that you can maximum insert 1000 records at a time. If you want to insert more than 1000 records you may need to loop through for every thousand records.

Syntax:
Insert into TableName Value (v1,v2,v3,v4), (v1,v2,v3,v4), (v1,v2,v3,v4)....  

C# Code:

public void InsertDataWithShortHandInsertQuery()  

        {  

            using (SqlConnection con = new SqlConnection(consString))  

            {  

                con.Open();  

                var sqlQuery = "Insert into Employees(EmpName,Age,Salary) Values";  

                var records = GetDataToInsert().Rows;  

                int counter = 0;  

  

                foreach (DataRow record in records)  

                {  

                    sqlQuery += String.Format("('{0}',{1},{2}),", record["EmpName"], record["Age"], record["Salary"]);  

                    counter++;  

                    if (counter%1000 == 0)  

                    {  

                        sqlQuery = sqlQuery.Substring(0, sqlQuery.LastIndexOf(","));  

                        using (SqlCommand cmd = new SqlCommand(sqlQuery, con))  

                        {  

                            cmd.ExecuteNonQuery();  

                        }  

                        sqlQuery = "Insert into Employees(EmpName,Age,Salary) Values";  

                    }  

                }  

                  

  

                con.Close();  

            }  

        }  

Which is best option SqlBulkCopy or Table Valued Parameters?
With  SqlBulkCopy we no need to change/add any database objects . But to use TVP first we need to create type object on the database. So if you trying to insert data throguh c# then we can  use SqlBulkCopy. In some .net core versions, SqlBulkCopy is not available. So in such cases we can use TVP. You can further read the performance difference between SqLBulkCopy and TVP by following the reference below.

http://stackoverflow.com/questions/2149897/performance-of-bcp-bulk-insert-vs-table-valued-parameters


Additional References: 
https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx
https://www.aspsnippets.com/Articles/SqlBulkCopy-Bulk-Copy-data-from-DataTable-DataSet-to-SQL-Server-Table-using-C-and-VBNet.aspx