Two Methods for batch data insertion by SQLServer

Insert statements are used to Insert a piece of data in SQLServer. However, if you want to Insert a bunch of data in batches, using Insert statements cyclically is not only inefficient, but also causes SQL system performance problems. The following describes two batch data insertion methods supported by SQLServer: Bulk and Table value parameters (Table-ValuedParameters ).

Insert statements are used to Insert a piece of data in SQL Server. However, if you want to Insert a bunch of data in batches, using Insert statements in a loop is not only inefficient, but also causes SQL system performance problems. The following describes two batch data insertion methods supported by SQL Server: Bulk and Table-Valued Parameters ).

Run the following script to create the test database and Table value parameters.
The Code is as follows:
-- Create DataBase
Create database BulkTestDB;
Go
Use BulkTestDB;
Go
-- Create Table
Create table BulkTestTable (
Id int primary key,
UserName nvarchar (32 ),
Pwd varchar (16 ))
Go
-- Create Table Valued
Create type BulkUdt AS TABLE
(Id int,
UserName nvarchar (32 ),
Pwd varchar (16 ))

The following code inserts 1 million data records using the simplest Insert statement:
The Code is as follows:
Stopwatch sw = new Stopwatch ();

SqlConnection sqlConn = new SqlConnection (
ConfigurationManager. ConnectionStrings ["ConnStr"]. ConnectionString); // connect to the database

SqlCommand sqlComm = new SqlCommand ();
SqlComm. CommandText = string. Format ("insert into BulkTestTable (Id, UserName, Pwd) values (@ p0, @ p1, @ p2)"); // parameterized SQL
SqlComm. Parameters. Add ("@ p0", SqlDbType. Int );
SqlComm. Parameters. Add ("@ p1", SqlDbType. NVarChar );
SqlComm. Parameters. Add ("@ p2", SqlDbType. VarChar );
SqlComm. CommandType = CommandType. Text;
SqlComm. Connection = sqlConn;
SqlConn. Open ();
Try
{
// Insert 1 million data records cyclically, insert 0.1 million data records each time, and insert 10 data records.
For (int multiply = 0; multiply <10; multiply ++)
{
For (int count = multiply * 100000; count <(multiply + 1) * 100000; count ++)
{

SqlComm. Parameters ["@ p0"]. Value = count;
SqlComm. Parameters ["@ p1"]. Value = string. Format ("User-{0}", count * multiply );
SqlComm. Parameters ["@ p2"]. Value = string. Format ("Pwd-{0}", count * multiply );
Sw. Start ();
SqlComm. ExecuteNonQuery ();
Sw. Stop ();
}
// After 0.1 million data entries are inserted, the insertion time is displayed.
Console. WriteLine (string. Format ("Elapsed Time is {0} Milliseconds", sw. ElapsedMilliseconds ));
}
}
Catch (Exception ex)
{
Throw ex;
}
Finally
{
SqlConn. Close ();
}

Console. ReadLine ();

The time consumed is shown as follows:

Because the operation is too slow, it takes 0.1 million milliseconds to insert 72390 entries, so I stopped it manually.

Next let's take a look at the use of Bulk insertion:

The bulk method is mainly used to cache all the data in the Table on the client, and then use SqlBulkCopy to insert the data in the Table to the database at a time.

The Code is as follows:
The Code is as follows:
Public static void BulkToDB (DataTable dt)
{
SqlConnection sqlConn = new SqlConnection (
ConfigurationManager. ConnectionStrings ["ConnStr"]. ConnectionString );
SqlBulkCopy bulkCopy = new SqlBulkCopy (sqlConn );
BulkCopy. DestinationTableName = "BulkTestTable ";
BulkCopy. BatchSize = dt. Rows. Count;

Try
{
SqlConn. Open ();
If (dt! = Null & dt. Rows. Count! = 0)
BulkCopy. WriteToServer (dt );
}
Catch (Exception ex)
{
Throw ex;
}
Finally
{
SqlConn. Close ();
If (bulkCopy! = Null)
BulkCopy. Close ();
}
}

Public static DataTable GetTableSchema ()
{
DataTable dt = new DataTable ();
Dt. Columns. AddRange (new DataColumn [] {
New DataColumn ("Id", typeof (int )),
New DataColumn ("UserName", typeof (string )),
New DataColumn ("Pwd", typeof (string ))});

Return dt;
}

Static void Main (string [] args)
{
Stopwatch sw = new Stopwatch ();
For (int multiply = 0; multiply <10; multiply ++)
{
DataTable dt = Bulk. GetTableSchema ();
For (int count = multiply * 100000; count <(multiply + 1) * 100000; count ++)
{
DataRow r = dt. NewRow ();
R [0] = count;
R [1] = string. Format ("User-{0}", count * multiply );
R [2] = string. Format ("Pwd-{0}", count * multiply );
Dt. Rows. Add (r );
}
Sw. Start ();
Bulk. BulkToDB (dt );
Sw. Stop ();
Console. WriteLine (string. Format ("Elapsed Time is {0} Milliseconds", sw. ElapsedMilliseconds ));
}

Console. ReadLine ();
}

The time consumed is shown as follows:


It can be seen that after Bulk is used, the efficiency and performance increase significantly. Using Insert to Insert 0.1 million data takes 72390 of the time, while using Bulk to Insert 1 million data takes 17583 of the time.

The efficiency of using Table value parameters will surprise you.

The table value parameter is a new feature of SQL Server 2008, TVPs for short. If you are not familiar with table value parameters, refer to the latest book online. I will also write a blog about table value parameters, however, we will not introduce the concept of Table value parameters too much this time. Let's get down to the truth and read the code:
The Code is as follows:
Public static void TableValuedToDB (DataTable dt)
{
SqlConnection sqlConn = new SqlConnection (
ConfigurationManager. ConnectionStrings ["ConnStr"]. ConnectionString );
Const string TSqlStatement =
"Insert into BulkTestTable (Id, UserName, Pwd)" +
"SELECT nc. Id, nc. UserName, nc. Pwd" +
"FROM @ NewBulkTestTvp AS nc ";
SqlCommand cmd = new SqlCommand (TSqlStatement, sqlConn );
SqlParameter catParam = cmd. Parameters. AddWithValue ("@ NewBulkTestTvp", dt );
CatParam. SqlDbType = SqlDbType. Structured;
// The Name Of The Table value parameter is BulkUdt, which is included in the SQL statement used in the preceding test environment.
CatParam. TypeName = "dbo. BulkUdt ";
Try
{
SqlConn. Open ();
If (dt! = Null & dt. Rows. Count! = 0)
{
Cmd. ExecuteNonQuery ();
}
}
Catch (Exception ex)
{
Throw ex;
}
Finally
{
SqlConn. Close ();
}
}

Public static DataTable GetTableSchema ()
{
DataTable dt = new DataTable ();
Dt. Columns. AddRange (new DataColumn [] {
New DataColumn ("Id", typeof (int )),
New DataColumn ("UserName", typeof (string )),
New DataColumn ("Pwd", typeof (string ))});

Return dt;
}

Static void Main (string [] args)
{
Stopwatch sw = new Stopwatch ();
For (int multiply = 0; multiply <10; multiply ++)
{
DataTable dt = TableValued. GetTableSchema ();
For (int count = multiply * 100000; count <(multiply + 1) * 100000; count ++)
{
DataRow r = dt. NewRow ();
R [0] = count;
R [1] = string. Format ("User-{0}", count * multiply );
R [2] = string. Format ("Pwd-{0}", count * multiply );
Dt. Rows. Add (r );
}
Sw. Start ();
TableValued. TableValuedToDB (dt );
Sw. Stop ();
Console. WriteLine (string. Format ("Elapsed Time is {0} Milliseconds", sw. ElapsedMilliseconds ));
}

Console. ReadLine ();
}

The time consumed is shown as follows:

5 seconds faster than Bulk.
This article is original from CSDN TJVictor