Reproduced Two ways SQL Server inserts data in bulk.

Inserting a single piece of data into SQL Server uses the INSERT statement, but if you want to bulk insert a bunch of data, looping through insert is inefficient and results in a system performance problem with SQL. The following are the two bulk data insertion methods supported by SQL Server: Bulk and table-valued parameters (table-valued Parameters).
Run the following script to establish the test database and table-valued parameters. Copy the code code as follows:--create database Create database bulktestdb; Go use Bulktestdb; Go--create table Create table bulktesttable (Id int primary key, UserName nvarchar (+), Pwd varchar) go--create tabl E valued CREATE TYPE Bulkudt as TABLE (Id int, UserName nvarchar (+), Pwd varchar (16))

Here we use the simplest INSERT statement for inserting 1 million data with the following code: Copy the Code as follows: Stopwatch SW = new Stopwatch ();

SqlConnection sqlconn = new SqlConnection (configurationmanager.connectionstrings["ConnStr"]. ConnectionString);//Connect 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 {//Loop insert 1 million data, insert 100,000 at a time, insert 10 times. for (int multiply = 0, multiply < multiply++) {for (int count = multiply * 100000; count < (multiply + 1) * 100 000; 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 (); }//Every 100,000 data is inserted, the time Console.WriteLine (string) for this insertion is displayed. Format ("Elapsed time is {0} Milliseconds", SW. Elapsedmilliseconds)); }} catch (Exception ex) {throw ex;} finally {sqlconn.close ();}

Console.ReadLine ();

The time-consuming diagram is as follows:

Time-consuming graph for inserting 100,000 data using INSERT statements

Since the operation is too slow to insert 100,000 is time-consuming 72390 milliseconds, so I manually forced to stop.

Here's a look at the use of bulk inserts:

The main idea of the bulk method is to insert the data in the table into the database by sqlbulkcopy the data in the table at the client, and then using the

The code is as follows:

Copy the code code as follows: public static void Bulktodb (DataTable dt) {SqlConnection sqlconn = new SqlConnection (Configurationmanager.con nectionstrings["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 D Atacolumn ("Pwd", typeof (String))});

return DT; }

static void Main (string[] args) {Stopwatch sw = new Stopwatch (); for (int multiply = 0; multiply < multiply++) {D Atatable 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-consuming diagram is as follows: time-consuming graph for inserting 1 million data using bulk

It can be seen that efficiency and performance increase significantly after using bulk. Inserting 100,000 data with insert takes 72390, and now it takes 17583 to insert 1 million data using bulk.

Finally, let's look at the efficiency of using table-valued parameters and you'll be surprised.

The

Table-valued parameter is a new SQL Server 2008 feature, referred to as TVPs. For friends who are not familiar with table-valued parameters, you can refer to the latest book online, I will also write a blog about table-valued parameters, but this time does not introduce the concept of table-valued parameters too much. Anyway, look at the code: Copy the code as follows: public static void Tablevaluedtodb (DataTable dt) {SqlConnection sqlconn = new SqlConnection (Configu rationmanager.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-valued parameter is Bulkudt, which is in the SQL that created the test environment above. 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 < multiply++) {D Atatable 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-consuming diagram is as follows:

Time-consuming graph for inserting 1 million data using table-valued parameters

It's 5 seconds faster than bulk.

For more information, refer to: http://blog.csdn.net/tjvictor/article/details/4360030