【SQL Server 批量插入数据】

Bertha 。 2022-09-23 15:53 157阅读 0赞

在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次，效率是越来越慢。

![20160721123121535][]

### 二.较快速的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次，效率快了很多。  
![20160721123454900][]

### 三.使用简称TVPs插入数据    ###

打开sqlserrver，执行以下脚本：

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

![20160721123709020][]

成功后在数据库中发现多了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

![20160721123938609][]

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

转载参考至TJVictor专栏：[http://blog.csdn.net/tjvictor/archive/2009/07/18/4360030.aspx][http_blog.csdn.net_tjvictor_archive_2009_07_18_4360030.aspx]

[20160721123121535]: /images/20220720/e2b4666571c348ca8d0ba61659bf773b.png
[20160721123454900]: /images/20220720/511a16d552c14352a0e217e2e2a2cacc.png
[20160721123709020]: /images/20220720/7f6dfab9cc414091b346b354a07eb8be.png
[20160721123938609]: /images/20220720/b5113df53f1148858e949b004fcfd655.png
[http_blog.csdn.net_tjvictor_archive_2009_07_18_4360030.aspx]: http://blog.csdn.net/tjvictor/archive/2009/07/18/4360030.aspx