在SQL Server 中插入一条数据使用Insert语句，但是如果想要批量插入一堆数据的话，循环使用Insert不仅效率低，而且会导致SQL一系统性能问题。下面介绍SQL Server支持的两种批量数据插入方法：Bulk和表值参数(Table-Valued Parameters)，高效插入数据。

新建数据库：

--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 

一.传统的INSERT方式

先看下传统的INSERT方式：一条一条的插入（性能消耗越来越大，速度越来越慢）

        //使用简单的Insert方法一条条插入 [慢]
        #region [ simpleInsert ]
        static void simpleInsert()
        {
            Console.WriteLine("使用简单的Insert方法一条条插入");
            Stopwatch sw = new Stopwatch();
            SqlConnection sqlconn = new SqlConnection("server=.;database=BulkTestDB;user=sa;password=123456;");
            SqlCommand sqlcmd = new SqlCommand();
            sqlcmd.CommandText = string.Format("insert into BulkTestTable(Id,UserName,Pwd)values(@p0,@p1,@p2)");
            sqlcmd.Parameters.Add("@p0", SqlDbType.Int);
            sqlcmd.Parameters.Add("@p1", SqlDbType.NVarChar);
            sqlcmd.Parameters.Add("@p2", SqlDbType.NVarChar);
            sqlcmd.CommandType = CommandType.Text;
            sqlcmd.Connection = sqlconn;
            sqlconn.Open();
            try
            {
                //循环插入1000条数据，每次插入100条，插入10次。  
                for (int multiply = 0; multiply < 10; multiply++)
                {
                    for (int count = multiply * 100; count < (multiply + 1) * 100; count++)
                    {
 
                        sqlcmd.Parameters["@p0"].Value = count;
                        sqlcmd.Parameters["@p1"].Value = string.Format("User-{0}", count * multiply);
                        sqlcmd.Parameters["@p2"].Value = string.Format("Pwd-{0}", count * multiply);
                        sw.Start();
                        sqlcmd.ExecuteNonQuery();
                        sw.Stop();
                    }
                    //每插入10万条数据后，显示此次插入所用时间  
                    Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));
                }
                Console.ReadKey();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
        #endregion

循环插入1000条数据，每次插入100条，插入10次，效率是越来越慢。

二.较快速的Bulk插入方式：

使用使用Bulk插入[ 较快 ]

        //使用Bulk插入的情况 [ 较快 ]
        #region [ 使用Bulk插入的情况 ]
        static void BulkToDB(DataTable dt)
        {
            Stopwatch sw = new Stopwatch();
            SqlConnection sqlconn = new SqlConnection("server=.;database=BulkTestDB;user=sa;password=123456;");
            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)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                sqlconn.Close();
                if (bulkCopy != null)
                {
                    bulkCopy.Close();
                }
            }
        }
        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 BulkInsert()
        {
            Console.WriteLine("使用简单的Bulk插入的情况");
            Stopwatch sw = new Stopwatch();
            for (int multiply = 0; multiply < 10; multiply++)
            {
                DataTable dt = GetTableSchema();
                for (int count = multiply * 100; count < (multiply + 1) * 100; 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();
                BulkToDB(dt);
                sw.Stop();
                Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));
            }
        }
        #endregion

循环插入1000条数据，每次插入100条，插入10次，效率快了很多。

三.使用简称TVPs插入数据

打开sqlserrver，执行以下脚本：

--Create Table Valued  
CREATE TYPE BulkUdt AS TABLE  
  (Id int,  
   UserName nvarchar(32),  
   Pwd varchar(16))  

成功后在数据库中发现多了BulkUdt的缓存表。

使用简称TVPs插入数据

        //使用简称TVPs插入数据 [最快]
        #region [ 使用简称TVPs插入数据 ]
        static void TbaleValuedToDB(DataTable dt)
        {
            Stopwatch sw = new Stopwatch();
            SqlConnection sqlconn = new SqlConnection("server=.;database=BulkTestDB;user=sa;password=123456;");
            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;
            catParam.TypeName = "dbo.BulkUdt";
            try
            {
                sqlconn.Open();
                if (dt != null && dt.Rows.Count != 0)
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("error>" + ex.Message);
            }
            finally
            {
                sqlconn.Close();
            }
        }
        static void TVPsInsert()
        {
            Console.WriteLine("使用简称TVPs插入数据");
            Stopwatch sw = new Stopwatch();
            for (int multiply = 0; multiply < 10; multiply++)
            {
                DataTable dt = GetTableSchema();
                for (int count = multiply * 100; count < (multiply + 1) * 100; 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();
                TbaleValuedToDB(dt);
                sw.Stop();
                Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));
            }
            Console.ReadLine();  
        }
        #endregion

循环插入1000条数据，每次插入100条，插入10次，效率是越来越慢，后面测试，将每次插入的数据量增大，会更大的体现TPVS插入的效率。

免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：mmqy2019@163.com进行举报，并提供相关证据，查实之后，将立刻删除涉嫌侵权内容。

猜你喜欢

• SQL Server中执行计划缓存要考虑什么，常见问题有哪些

  这篇文章主要介绍了浅析SQL Server中的执行计划缓存（下）的相关资料,需要的朋友可以参考下

• SQL Server中怎样进行树形查询，SQL语句是什么

  这篇文章主要介绍了SQL Server 树形表非循环递归查询的实例详解的相关资料,本文介绍的非常详细具有参考借鉴价值，需要的朋友可以参考下

• SQL常见的运算符有几种，分别是什么

  SQL 运算符，运算符是保留字或主要用于 SQL 语句的 WHERE 子句中的字符，用于执行操作，例如：比较和算术运算。 这些运算符用于指定 SQL 语句中的条件，并用作语句中多个条件的连词。

• SQL内部连接INNER JOIN语法是怎样，如何使用

  SQL 内部链接INNER JOIN关键字选择两个表中具有匹配值的记录。_下文的讲解详细，步骤过程清晰，对大家进一步学习和理解相关知识有一定的帮助。有这方面学习需要的朋友就继续往下看吧！

• SQL视图的创建、更新和删除视图操作是怎样的

  视图是可视化的表。本章讲解如何创建、更新和删除视图。SQL CREATE VIEW 语句，在 SQL 中，视图是基于 SQL 语句的结果集的可视化表。视图包含行和列，就像真正的表一样。视图中的字段是一个或多个数据库中真实表中的字段。您可以添加 SQL 函数，在哪里添加，并将语句连接到视图，或者您可以呈现数据，就像数据来自单个表一样。