C#批量插入数据到Sqlserver中的四种方式

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› c#›C#教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

本篇，我将来讲解一下在Sqlserver中批量插入数据。

先创建一个用来测试的数据库和表，为了让插入数据更快，表中主键采用的是GUID，表中没有创建任何索引。GUID必然是比自增长要快的，因为你生成一个GUID算法所花的时间肯定比你从数据表中重新查询上一条记录的ID的值然后再进行加1运算要少。而如果存在索引的情况下，每次插入记录都会进行索引重建，这是非常耗性能的。如果表中无可避免的存在索引，我们可以通过先删除索引，然后批量插入，最后再重建索引的方式来提高效率。

create database CarSYS;    
go    
use CarSYS;    
go 
CREATE TABLE Product(
Id UNIQUEIDENTIFIER PRIMARY KEY,
NAME VARCHAR(50) NOT NULL,
Price DECIMAL(18,2) NOT NULL
)

我们通过SQL脚本来插入数据，常见如下四种方式。

方式一：一条一条插入，性能最差，不建议使用。

INSERT INTO Product(Id,Name,Price) VALUES(newid(),'牛栏1段',160);
INSERT INTO Product(Id,Name,Price) VALUES(newid(),'牛栏2段',260);
......

方式二：insert bulk

语法如下：

    BULK INSERT [ [ 'database_name'.][ 'owner' ].]{ 'table_name' FROM 'data_file' }       
    WITH  (   
            [ BATCHSIZE [ = batch_size ] ],       
            [ CHECK_CONSTRAINTS ],           
            [ CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ] ],   
            [ DATAFILETYPE [ = 'char' | 'native'| 'widechar' | 'widenative' ] ],               
            [ FIELDTERMINATOR [ = 'field_terminator' ] ],   
            [ FIRSTROW [ = first_row ] ],   
            [ FIRE_TRIGGERS ],   
            [ FORMATFILE = 'format_file_path' ],   
            [ KEEPIDENTITY ],   
            [ KEEPNULLS ],   
            [ KILOBYTES_PER_BATCH [ = kilobytes_per_batch ] ],      
            [ LASTROW [ = last_row ] ],   
            [ MAXERRORS [ = max_errors ] ],   
            [ ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ],     
            [ ROWS_PER_BATCH [ = rows_per_batch ] ],   
            [ ROWTERMINATOR [ = 'row_terminator' ] ],             
            [ TABLOCK ],   
    )

方式一：逐条插入

        #region 方式一
        static void InsertOne()
        {
            Console.WriteLine("采用一条一条插入的方式实现");
            Stopwatch sw = new Stopwatch();

            using (SqlConnection conn = new SqlConnection(StrConnMsg)) //using中会自动Open和Close 连接。
            {
                string sql = "INSERT INTO Product(Id,Name,Price) VALUES(newid(),@p,@d)";
                conn.Open();
                for (int i = 0; i < totalRow; i++)
                {
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        cmd.Parameters.AddWithValue("@p", "商品" + i);
                        cmd.Parameters.AddWithValue("@d", i);
                        sw.Start();
                        cmd.ExecuteNonQuery();
                        Console.WriteLine(string.Format("插入一条记录，已耗时{0}毫秒", sw.ElapsedMilliseconds));
                    }
                    if (i == getRow)
                    {
                        sw.Stop();
                        break;
                    }
                }
            }
            Console.WriteLine(string.Format("插入{0}条记录，每{4}条的插入时间是{1}毫秒,预估总得插入时间是{2}毫秒，{3}分钟",
 totalRow, sw.ElapsedMilliseconds, ((sw.ElapsedMilliseconds / getRow) * totalRow), GetMinute((sw.ElapsedMilliseconds / getRow * totalRow)), getRow));
        }
        static int GetMinute(long l)
        {
            return (Int32)l / 60000;
        } 
        #endregion

运行结果如下：

我们会发现插入100w条记录，预计需要50分钟时间，每插入一条记录大概需要3毫秒左右。

方式二：使用SqlBulk

        #region 方式二
        static void InsertTwo()
        {
            Console.WriteLine("使用Bulk插入的实现方式");
            Stopwatch sw = new Stopwatch();
            DataTable dt = GetTableSchema(); 

            using (SqlConnection conn = new SqlConnection(StrConnMsg))
            {
                SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
                bulkCopy.DestinationTableName = "Product";
                bulkCopy.BatchSize = dt.Rows.Count;
                conn.Open();
                sw.Start();

                for (int i = 0; i < totalRow;i++ )
                {
                    DataRow dr = dt.NewRow();
                    dr[0] = Guid.NewGuid();
                    dr[1] = string.Format("商品", i);
                    dr[2] = (decimal)i;
                    dt.Rows.Add(dr);
                }
                    if (dt != null && dt.Rows.Count != 0)
                    {
                        bulkCopy.WriteToServer(dt);
                        sw.Stop();
                    }
                    Console.WriteLine(string.Format("插入{0}条记录共花费{1}毫秒，{2}分钟", totalRow, sw.ElapsedMilliseconds, GetMinute(sw.ElapsedMilliseconds)));
            }
        }
        static DataTable GetTableSchema()
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[] {   
        new DataColumn("Id",typeof(Guid)),  
        new DataColumn("Name",typeof(string)),  
        new DataColumn("Price",typeof(decimal))});
            return dt;
        }
        #endregion

运行结果如下：

插入100w条记录才8s多，是不是很溜。

打开Sqlserver Profiler跟踪，会发现执行的是如下语句：

insert bulk Product ([Id] UniqueIdentifier, [NAME] VarChar(50) COLLATE Chinese_PRC_CI_AS, [Price] Decimal(18,2))

方式三：使用TVPs(表值参数）插入数据

从sqlserver 2008起开始支持TVPs。创建缓存表ProductTemp ，执行如下SQL。

CREATE TYPE ProductTemp AS  TABLE(
Id UNIQUEIDENTIFIER PRIMARY KEY,
NAME VARCHAR(50) NOT NULL,
Price DECIMAL(18,2) NOT NULL
)

执行完成之后，会发现在数据库CarSYS下面多了一张缓存表ProductTemp

可见插入100w条记录共花费了11秒多。

方式四：拼接SQL

此种方法在C#中有限制，一次性只能批量插入1000条，所以就得分段进行插入。

        #region 方式四
        static void InsertFour()
        {
            Console.WriteLine("采用拼接批量SQL插入的方式实现");
            Stopwatch sw = new Stopwatch();

            using (SqlConnection conn = new SqlConnection(StrConnMsg)) //using中会自动Open和Close 连接。
            {
                conn.Open();
                sw.Start();
                for (int j = 0; j < totalRow / getRow;j++ )
                {
                    StringBuilder sb = new StringBuilder();
                    sb.Append("INSERT INTO Product(Id,Name,Price) VALUES");
                    using (SqlCommand cmd = new SqlCommand())
                    {
                       
                        for (int i = 0; i < getRow; i++)
                        {
                            sb.AppendFormat("(newid(),'商品{0}',{0}),", j*i+i);
                        }
                        cmd.Connection = conn;
                        cmd.CommandText = sb.ToString().TrimEnd(',');
                        cmd.ExecuteNonQuery();
                    }
                }
                sw.Stop();
                Console.WriteLine(string.Format("插入{0}条记录，共耗时{1}毫秒",totalRow,sw.ElapsedMilliseconds));
            }
        }
        #endregion

运行结果如下：

我们可以看到大概花费了10分钟。虽然在方式一的基础上，性能有了较大的提升，但是显然还是不够快。

总结：大数据批量插入方式一和方式四尽量避免使用，而方式二和方式三都是非常高效的批量插入数据方式。其都是通过构建DataTable的方式插入的，而我们知道DataTable是存在内存中的，所以当数据量特别特别大，大到内存中无法一次性存储的时候，可以分段插入。比如需要插入9千万条数据，可以分成9段进行插入，一次插入1千万条。而在for循环中直接进行数据库操作，我们是应该尽量避免的。每一次数据库的连接、打开和关闭都是比较耗时的，虽然在C#中存在数据库连接池，也就是当我们使用using或者conn.Close()，进行释放连接时，其实并没有真正关闭数据库连接，它只是让连接以类似于休眠的方式存在，当再次操作的时候，会从连接池中找一个休眠状态的连接，唤醒它，这样可以有效的提高并发能力，减少连接损耗。而连接池中的连接数，我们都是可以配置的。

源码下载：http://pan.baidu.com/s/1slm1wPv

create database CarSYS;    
go    
use CarSYS;    
go 
CREATE TABLE Product(
Id UNIQUEIDENTIFIER PRIMARY KEY,
NAME VARCHAR(50) NOT NULL,
Price DECIMAL(18,2) NOT NULL
)

我们通过SQL脚本来插入数据，常见如下四种方式。

方式一：一条一条插入，性能最差，不建议使用。

INSERT INTO Product(Id,Name,Price) VALUES(newid(),'牛栏1段',160);
INSERT INTO Product(Id,Name,Price) VALUES(newid(),'牛栏2段',260);
......

方式二：insert bulk

语法如下：

    BULK INSERT [ [ 'database_name'.][ 'owner' ].]{ 'table_name' FROM 'data_file' }       
    WITH  (   
            [ BATCHSIZE [ = batch_size ] ],       
            [ CHECK_CONSTRAINTS ],           
            [ CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ] ],   
            [ DATAFILETYPE [ = 'char' | 'native'| 'widechar' | 'widenative' ] ],               
            [ FIELDTERMINATOR [ = 'field_terminator' ] ],   
            [ FIRSTROW [ = first_row ] ],   
            [ FIRE_TRIGGERS ],   
            [ FORMATFILE = 'format_file_path' ],   
            [ KEEPIDENTITY ],   
            [ KEEPNULLS ],   
            [ KILOBYTES_PER_BATCH [ = kilobytes_per_batch ] ],      
            [ LASTROW [ = last_row ] ],   
            [ MAXERRORS [ = max_errors ] ],   
            [ ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ],     
            [ ROWS_PER_BATCH [ = rows_per_batch ] ],   
            [ ROWTERMINATOR [ = 'row_terminator' ] ],             
            [ TABLOCK ],   
    )

方式一：逐条插入

        #region 方式一
        static void InsertOne()
        {
            Console.WriteLine("采用一条一条插入的方式实现");
            Stopwatch sw = new Stopwatch();

            using (SqlConnection conn = new SqlConnection(StrConnMsg)) //using中会自动Open和Close 连接。
            {
                string sql = "INSERT INTO Product(Id,Name,Price) VALUES(newid(),@p,@d)";
                conn.Open();
                for (int i = 0; i < totalRow; i++)
                {
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        cmd.Parameters.AddWithValue("@p", "商品" + i);
                        cmd.Parameters.AddWithValue("@d", i);
                        sw.Start();
                        cmd.ExecuteNonQuery();
                        Console.WriteLine(string.Format("插入一条记录，已耗时{0}毫秒", sw.ElapsedMilliseconds));
                    }
                    if (i == getRow)
                    {
                        sw.Stop();
                        break;
                    }
                }
            }
            Console.WriteLine(string.Format("插入{0}条记录，每{4}条的插入时间是{1}毫秒,预估总得插入时间是{2}毫秒，{3}分钟",
 totalRow, sw.ElapsedMilliseconds, ((sw.ElapsedMilliseconds / getRow) * totalRow), GetMinute((sw.ElapsedMilliseconds / getRow * totalRow)), getRow));
        }
        static int GetMinute(long l)
        {
            return (Int32)l / 60000;
        } 
        #endregion

运行结果如下：

我们会发现插入100w条记录，预计需要50分钟时间，每插入一条记录大概需要3毫秒左右。

方式二：使用SqlBulk

        #region 方式二
        static void InsertTwo()
        {
            Console.WriteLine("使用Bulk插入的实现方式");
            Stopwatch sw = new Stopwatch();
            DataTable dt = GetTableSchema(); 

            using (SqlConnection conn = new SqlConnection(StrConnMsg))
            {
                SqlBulkCopy bulkCopy = new SqlBulkCopy(conn);
                bulkCopy.DestinationTableName = "Product";
                bulkCopy.BatchSize = dt.Rows.Count;
                conn.Open();
                sw.Start();

                for (int i = 0; i < totalRow;i++ )
                {
                    DataRow dr = dt.NewRow();
                    dr[0] = Guid.NewGuid();
                    dr[1] = string.Format("商品", i);
                    dr[2] = (decimal)i;
                    dt.Rows.Add(dr);
                }
                    if (dt != null && dt.Rows.Count != 0)
                    {
                        bulkCopy.WriteToServer(dt);
                        sw.Stop();
                    }
                    Console.WriteLine(string.Format("插入{0}条记录共花费{1}毫秒，{2}分钟", totalRow, sw.ElapsedMilliseconds, GetMinute(sw.ElapsedMilliseconds)));
            }
        }
        static DataTable GetTableSchema()
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[] {   
        new DataColumn("Id",typeof(Guid)),  
        new DataColumn("Name",typeof(string)),  
        new DataColumn("Price",typeof(decimal))});
            return dt;
        }
        #endregion

运行结果如下：

插入100w条记录才8s多，是不是很溜。

打开Sqlserver Profiler跟踪，会发现执行的是如下语句：

insert bulk Product ([Id] UniqueIdentifier, [NAME] VarChar(50) COLLATE Chinese_PRC_CI_AS, [Price] Decimal(18,2))

方式三：使用TVPs(表值参数）插入数据

从sqlserver 2008起开始支持TVPs。创建缓存表ProductTemp ，执行如下SQL。

CREATE TYPE ProductTemp AS  TABLE(
Id UNIQUEIDENTIFIER PRIMARY KEY,
NAME VARCHAR(50) NOT NULL,
Price DECIMAL(18,2) NOT NULL
)

执行完成之后，会发现在数据库CarSYS下面多了一张缓存表ProductTemp

可见插入100w条记录共花费了11秒多。

方式四：拼接SQL

此种方法在C#中有限制，一次性只能批量插入1000条，所以就得分段进行插入。

        #region 方式四
        static void InsertFour()
        {
            Console.WriteLine("采用拼接批量SQL插入的方式实现");
            Stopwatch sw = new Stopwatch();

            using (SqlConnection conn = new SqlConnection(StrConnMsg)) //using中会自动Open和Close 连接。
            {
                conn.Open();
                sw.Start();
                for (int j = 0; j < totalRow / getRow;j++ )
                {
                    StringBuilder sb = new StringBuilder();
                    sb.Append("INSERT INTO Product(Id,Name,Price) VALUES");
                    using (SqlCommand cmd = new SqlCommand())
                    {
                       
                        for (int i = 0; i < getRow; i++)
                        {
                            sb.AppendFormat("(newid(),'商品{0}',{0}),", j*i+i);
                        }
                        cmd.Connection = conn;
                        cmd.CommandText = sb.ToString().TrimEnd(',');
                        cmd.ExecuteNonQuery();
                    }
                }
                sw.Stop();
                Console.WriteLine(string.Format("插入{0}条记录，共耗时{1}毫秒",totalRow,sw.ElapsedMilliseconds));
            }
        }
        #endregion

运行结果如下：

我们可以看到大概花费了10分钟。虽然在方式一的基础上，性能有了较大的提升，但是显然还是不够快。

源码下载：http://pan.baidu.com/s/1slm1wPv

鲜花

握手

雷人

路过

鸡蛋

该文章已有0人参与评论

请发表评论

全部评论

专题导读

More+

10-27 六六分期app的软件客服如何联系？(六六分期

11-06 可心卡盟:win10系统火狐flash插件崩溃怎么

11-06 亲亲特价:怎么删除回收站图标

11-06 济南大学虚拟社区:鲁大师节能降温的具体办

11-06 xlueops.exe:无线网络安装向导

11-06 女斗合众国:win7系统cf与主机连接不稳定怎

11-06 0xc000022-[cf烟雾头]cf怎么调烟雾头

11-06 qizideyouhuo:应用程序无法正常启动0xc0000

11-06 ipz-185:win7系统vcf文件怎么打开

11-06 傻哥蹦迪:win10系统s4怎么打开usb调试

11-06 八神浩树gtaste:回收站清空了怎么恢复

11-06 妖尾之黑色守护:win10系统电脑没有1440x900

11-06 校园至尊魔王小说:win7系统浏览网页时字体

11-06 女斗合众国:win10系统访问共享文件夹提示请

11-06 tokyo hot n0654:恢复win7系统默认字体一招

11-06 雨酷仙境:设置win7系统转移临时文件夹腾出

11-06 阿穆纳伊之杖:win7系统开始菜单在右边还原

11-06 tunespotting:win10系统火狐flash插件总是

11-06 甘尔葛分析师：计谋网站seo关键词暴涨有什

11-06 蔡贵霖: 计谋网站seo关键词暴涨有什么秘密

11-06 博益网首页:ao3网页版进入不了解决方法

11-06 漏斗子专栏: 网站数据分析小白易懂精华篇

11-06 见证双虹怎么做:win7系统开启telnet命令的

11-06 颾狐蝶蜋:系统资源不足无法完成请求的服务

11-06 国光中学校歌:提交网站到alexa查询详细步骤

11-06 西安有情天:静态网页和动态网页的区别

11-06 红木雅尚斋:外部链接构造对网站的好处

11-06 前官礼遇：防止域名劫持–增强域安全性的10

11-06 密传二转答案: 中文分词算法有哪些

11-06 金泉家园邮编:百度快照劫持的表现及应对方

C#编码标准－－命名约定和风格发布时间：2022-07-10

C#组件系列——又一款Excel处理神器Spire.XLS，你值得拥有发布时间：2022-07-10

剪的笔顺,诠释剪的笔画,认识剪的部首

1 六六分期app的软件客服如何联系？(六六分期

六六分期app的软件客服如何联系？不知道吗？加qq群【895510560】即可！标题：六六分期

阅读：19173|2023-10-27

2 可心卡盟:win10系统火狐flash插件崩溃怎么

今天小编告诉大家如何处理win10系统火狐flash插件总是崩溃的问题，可能很多用户都不知

阅读：9985|2022-11-06

3 亲亲特价:怎么删除回收站图标

今天小编告诉大家如何对win10系统删除桌面回收站图标进行设置，可能很多用户都不知道

阅读：8324|2022-11-06

4 济南大学虚拟社区:鲁大师节能降温的具体办

今天小编告诉大家如何对win10系统电脑设置节能降温的设置方法，想必大家都遇到过需要

阅读：8694|2022-11-06

5 xlueops.exe:无线网络安装向导

我们在使用xp系统的过程中,经常需要对xp系统无线网络安装向导设置进行设置，可能很多

阅读：8637|2022-11-06

6 女斗合众国:win7系统cf与主机连接不稳定怎

今天小编告诉大家如何处理win7系统玩cf老是与主机连接不稳定的问题，可能很多用户都不

阅读：9654|2022-11-06

7 0xc000022-[cf烟雾头]cf怎么调烟雾头

电脑对日常生活的重要性小编就不多说了，可是一旦碰到win7系统设置cf烟雾头的问题，很

阅读：8621|2022-11-06

8 qizideyouhuo:应用程序无法正常启动0xc0000

我们在日常使用电脑的时候，有的小伙伴们可能在打开应用的时候会遇见提示应用程序无法

阅读：7998|2022-11-06

9 ipz-185:win7系统vcf文件怎么打开

今天小编告诉大家如何对win7系统打开vcf文件进行设置，可能很多用户都不知道怎么对win

阅读：8652|2022-11-06

10 傻哥蹦迪:win10系统s4怎么打开usb调试

今天小编告诉大家如何对win10系统s4开启USB调试模式进行设置，可能很多用户都不知道怎

阅读：7534|2022-11-06

客服电话

电子邮件

C#批量插入数据到Sqlserver中的四种方式

方式一：逐条插入

方式二：使用SqlBulk

方式三：使用TVPs(表值参数）插入数据

方式四：拼接SQL

方式一：逐条插入

方式二：使用SqlBulk

方式三：使用TVPs(表值参数）插入数据

方式四：拼接SQL

请发表评论

全部评论

上一篇：

下一篇：

微信小程序问题

kojino/Harvard-Robust-Machine-Learning:

cescoffier/puppet-nexus: A Puppet Module

文的笔顺,诠释文的笔画,解读文的部首

Cassolotl/sentient.cloud · GitHub

剪的笔顺,诠释剪的笔画,认识剪的部首

六六分期app的软件客服如何联系？(六六分期

florent37/ViewAnimator: A fluent Android

florent37/Shrine-MaterialDesign2: implem

CVE-2020-36276

SimpleSoftwareIO/simple-sms: Send and re

关于我们

产品与服务

解决方案

139-2527-9053