SQL Server批量插入数据案例详解_MsSql

来源:脚本之家  责任编辑:小易  

在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插入的效率。

到此这篇关于SQL Server批量插入数据案例详解的文章就介绍到这了,更多相关SQL Server批量插入数据内容请搜索真格学网以前的文章或继续浏览下面的相关文章希望大家以后多多支持真格学网!

您可能感兴趣的文章:SQL Server 批量插入数据的完美解决方案SQLServer2008存储过程实现数据插入与更新Python实现读取SQLServer数据并插入到MongoDB数据库的方法示例SQLServer中防止并发插入重复数据的方法详解详解C#批量插入数据到Sqlserver中的四种方式

  • 本文相关:
  • mssql数据库获取text字段的内容长度
  • sql server 使用触发器(trigger)发送电子邮件步骤详解
  • sql数据库的高级sql注入的一些知识
  • 更改sql server更改当前数据库的所有者:sp_changedbowner
  • 分区表场景下的 sql 优化
  • sql数据库不能直接用instr函数
  • sqlserver查询所有数据库名,表名,和字段名的语句
  • sql 时间函数 整理的比较全了
  • sql学习第一天——sql 将变量定义为table类型(虚拟表)
  • sql根据指定分隔符分解字符串实现步骤
  • sql server中批量插入与更新两种解决方案分享(存储过程)
  • sql server有批量插入和批量更新的sql语句吗
  • sql server有批量插入和批量更新的sql语句吗
  • SqlServer中批量替换被插入的木马记录
  • SQLserver 大批量更新插入的时候游标怎么优化
  • sql server 2008 插入数据 ?
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页sql server 批量插入数据的完美解决方案sqlserver2008存储过程实现数据插入与更新python实现读取sqlserver数据并插入到mongodb数据库的方法示例sqlserver中防止并发插入重复数据的方法详解详解c#批量插入数据到sqlserver中的四种方式mssql数据库获取text字段的内容长度sql server 使用触发器(trigger)发送电子邮件步骤详解sql数据库的高级sql注入的一些知识更改sql server更改当前数据库的所有者:sp_changedbowner分区表场景下的 sql 优化sql数据库不能直接用instr函数sqlserver查询所有数据库名,表名,和字段名的语句sql 时间函数 整理的比较全了sql学习第一天——sql 将变量定义为table类型(虚拟表)sql根据指定分隔符分解字符串实现步骤sql server 2012 安装图解教程(附sql server数据库入门学习总结microsoft sql server 2012 数据win7系统安装sqlserver2000的详细sql重复记录查询的几种方法sqlserver中distinct的用法(不重sql server错误代码大全及解释(sql-order by 多字段排序(升序、sql将一个表中的数据插入到另一个用sql语句添加删除修改字段、一些sql server视图的讲解sqlserver 不能将值null插入列id(列不允许sql server中数据行批量插入脚本的存储实必须会的sql语句(三) 数据插入sqlserver四舍五入使用round函数及cast和sql server 2012 数据库所有表里查找某字sqlserver 查询时日期格式化语句sql分页语句的三种方案sql server ltrim(rtrim()) 去不掉空格的sqlserver 2008数据库降级到2005低版本
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved