Shulou(Shulou.com)05/31 Report--

In order to solve the problem of how to insert data in bulk in SQLServer, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

Run the following script to create the test database and table-valued 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))

Let's use the simplest Insert statement to insert 1 million pieces of data, as follows:

Stopwatch sw = new Stopwatch (); SqlConnection sqlConn = new SqlConnection (ConfigurationManager.ConnectionStrings ["ConnStr"] .ConnectionString); / / connection database SqlCommand sqlComm = new SqlCommand (); sqlComm.CommandText = string.Format ("insert into BulkTestTable (Id,UserName,Pwd) values (@ p0 dagger p1 and 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 pieces of data in a loop, 100000 at a time, 10 times. For (int multiply = 0; multiply < 10; multiply++) {for (int count = multiply * 1000000; count < (multiply+ 1) * 1000000; 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 () } / / for every 100000 pieces of data inserted, the Console.WriteLine (string.Format ("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));}} catch (Exception ex) {throw ex;} finally {sqlConn.Close ();} Console.ReadLine ()

Because it was too slow, it took 72390 milliseconds to insert 100000 messages, so I stopped it manually. Let's take a look at the use of Bulk insertion: the main idea of the bulk method is to cache the data in Table on the client side, and then use SqlBulkCopy to insert the data in Table into the database code as follows: copy the code 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-consuming diagram is as follows: it can be seen that the efficiency and performance have improved significantly after using Bulk. It takes 72390 to insert 100000 data using Insert, whereas it now takes 17583 to insert 1 million data using Bulk. Finally, if you look at the efficiency of using table-valued parameters, you will be surprised. The table-valued parameter is a new feature of SQL Server 2008, 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 I will not introduce the concept of table-valued parameters too much. Back to the point, look at the code: copy the code 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-valued parameter is BulkUdt, which is found in the SQL above to set up the 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 ();}

This is the answer to the question about how to insert data in bulk in SQLServer. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.