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

How to insert data in bulk in SQL, many novices are not very clear about this. In order to help you solve this problem, the following editor will explain it in detail. People with this need can come and learn. I hope you can get something.

1. Create a table.

two。 Create a table-valued parameter type

We open the query analyzer and execute the following code in the query analyzer:

Create Type PassportTableType as Table (PassportKey nvarchar (50))

After the execution is successful, we open the Enterprise Manager and expand the following nodes in order-- database, expand programmability, type, and user-defined table type. We can see that the table value type we created is as shown in the following figure:

It means that we have successfully created the table value type.

3. Write stored procedures

The code for the stored procedure is as follows: copy the code as follows: USE [TestInsert] GO / * Object: StoredProcedure [dbo]. [CreatePassportWithTVP] Script Date: 03 * / SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO-- =-- Author:-- Create date:-- Description:-- Create PROCEDURE [dbo]. [CreatePassportWithTVP] @ TVP PassportTableType readonly AS BEGIN SET NOCOUNT ON; Insert into Passport (PassportKey) select PassportKey from @ TVP END

Perhaps in the query analyzer, the smart prompt will indicate that there is a problem with the table value type and there will be a red underscore (see figure below). Ignore it and continue to run our code to complete the creation of the stored procedure.

4. Write code to call the stored procedure. The code for the insertion of the three databases is as follows. Due to the tight time, the code may not be easy to read. I added some comments to the special code. The copy code is as follows: using System; using System.Diagnostics; using System.Data; using System.Data.SqlClient; using com.DataAccess; namespace ConsoleAppInsertTest {class Program {static string connectionString = SqlHelper.ConnectionStringLocalTransaction; / / database connection string static int count = 1000000; / / number of inserted static void Main (string [] args) {/ / long commonInsertRunTime = CommonInsert () / / Console.WriteLine (string.Format ("normal time to insert {1} pieces of data is {0} milliseconds", commonInsertRunTime, count); long sqlBulkCopyInsertRunTime = SqlBulkCopyInsert (); Console.WriteLine ("time to insert {1} pieces of data using SqlBulkCopy is {0} milliseconds", sqlBulkCopyInsertRunTime, count); long TVPInsertRunTime = TVPInsert () Console.WriteLine (string.Format ("the time it takes to insert {1} pieces of data using table-valued mode (TVP) is {0} milliseconds", TVPInsertRunTime, count);} / ordinary call stored procedure inserts data / private static long CommonInsert () {Stopwatch stopwatch = new Stopwatch (); stopwatch.Start (); string passportKey; for (int I = 0; I < count; itemized +) {passportKey = Guid.NewGuid (). ToString () SqlParameter [] sqlParameter = {new SqlParameter ("@ passport", passportKey)}; SqlHelper.ExecuteNonQuery (connectionString, CommandType.StoredProcedure, "CreatePassport", sqlParameter);} stopwatch.Stop (); return stopwatch.ElapsedMilliseconds;} / insert data in SqlBulkCopy mode / private static long SqlBulkCopyInsert () {Stopwatch stopwatch = new Stopwatch (); stopwatch.Start (); DataTable dataTable = GetTableSchema (); string passportKey; for (int I = 0; I < count) ToString (); DataRow dataRow = dataTable.NewRow (); dataRow [0] = passportKey; dataTable.Rows.Add (dataRow);} SqlBulkCopy sqlBulkCopy = new SqlBulkCopy (connectionString); sqlBulkCopy.DestinationTableName = "Passport"; sqlBulkCopy.BatchSize = dataTable.Rows.Count; SqlConnection sqlConnection = new SqlConnection (connectionString); sqlConnection.Open (); if (sqlBulkCopy.WriteToServer (dataTable);} sqlBulkCopy.Close (); sqlConnection.Close (); stopwatch.Stop () Return stopwatch.ElapsedMilliseconds;} private static long TVPInsert () {Stopwatch stopwatch = new Stopwatch (); stopwatch.Start (); DataTable dataTable = GetTableSchema (); string passportKey; for (int I = 0; I < count; iTunes +) {passportKey = Guid.NewGuid (). ToString (); DataRow dataRow = dataTable.NewRow (); dataRow [0] = passportKey; dataTable.Rows.Add (dataRow);} SqlParameter [] sqlParameter = {new SqlParameter ("@ TVP", dataTable)}; SqlHelper.ExecuteNonQuery (connectionString, CommandType.StoredProcedure, "CreatePassportWithTVP", sqlParameter) Stopwatch.Stop (); return stopwatch.ElapsedMilliseconds;} private static DataTable GetTableSchema () {DataTable dataTable = new DataTable (); dataTable.Columns.AddRange (new DataColumn [] {new DataColumn ("PassportKey")}); return dataTable;}

The more mysterious code is actually the following two lines, which is the stored procedure that passes a dataTable as an argument to our stored procedure. It's easy.

SqlParameter [] sqlParameter = {new SqlParameter ("@ TVP", dataTable)}; SqlHelper.ExecuteNonQuery (connectionString, CommandType.StoredProcedure, "CreatePassportWithTVP", sqlParameter); 5. Test and record the test results of the first group of tests, insert record number 1000 second group test, insert record number 10000 third group test, insert record number 1000000. Is it helpful for you to read the above? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.