请选择 进入手机版 | 继续访问电脑版

[MsSql] SQL Server批量插入数据案例详解

[复制链接]
查看209 | 回复31 | 2021-9-17 12:49:30 | 显示全部楼层 |阅读模式

在SQL Server 中插入一条数据使用 Insert语句,但是假如 想要批量插入一堆数据的话,循环使用 Insert不仅服从 低,而且会导致SQL一体系 性能题目 。下面先容 SQL Server支持的两种批量数据插入方法:Bulk和表值参数(Table-Valued Parameters),高效插入数据。

新建数据库:

  1. --Create DataBase
  2. create database BulkTestDB;
  3. go
  4. use BulkTestDB;
  5. go
  6. --Create Table
  7. Create table BulkTestTable(
  8. Id int primary key,
  9. UserName nvarchar(32),
  10. Pwd varchar(16))
  11. go
复制代码

一.传统的INSERT方式

先看下传统的INSERT方式:一条一条的插入(性能斲丧 越来越大,速率 越来越慢)

  1. //使用简单的Insert方法一条条插入 [慢]
  2. #region [ simpleInsert ]
  3. static void simpleInsert()
  4. {
  5. Console.WriteLine("使用简单的Insert方法一条条插入");
  6. Stopwatch sw = new Stopwatch();
  7. SqlConnection sqlconn = new SqlConnection("server=.;database=BulkTestDB;user=sa;password=123456;");
  8. SqlCommand sqlcmd = new SqlCommand();
  9. sqlcmd.CommandText = string.Format("insert into BulkTestTable(Id,UserName,Pwd)values(@p0,@p1,@p2)");
  10. sqlcmd.Parameters.Add("@p0", SqlDbType.Int);
  11. sqlcmd.Parameters.Add("@p1", SqlDbType.NVarChar);
  12. sqlcmd.Parameters.Add("@p2", SqlDbType.NVarChar);
  13. sqlcmd.CommandType = CommandType.Text;
  14. sqlcmd.Connection = sqlconn;
  15. sqlconn.Open();
  16. try
  17. {
  18. //循环插入1000条数据,每次插入100条,插入10次。
  19. for (int multiply = 0; multiply < 10; multiply++)
  20. {
  21. for (int count = multiply * 100; count < (multiply + 1) * 100; count++)
  22. {
  23. sqlcmd.Parameters["@p0"].Value = count;
  24. sqlcmd.Parameters["@p1"].Value = string.Format("User-{0}", count * multiply);
  25. sqlcmd.Parameters["@p2"].Value = string.Format("Pwd-{0}", count * multiply);
  26. sw.Start();
  27. sqlcmd.ExecuteNonQuery();
  28. sw.Stop();
  29. }
  30. //每插入10万条数据后,显示此次插入所用时间
  31. Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));
  32. }
  33. Console.ReadKey();
  34. }
  35. catch (Exception ex)
  36. {
  37. Console.WriteLine(ex.Message);
  38. }
  39. }
  40. #endregion
复制代码

循环插入1000条数据,每次插入100条,插入10次,服从 是越来越慢。

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

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

使用 使用 Bulk插入[ 较快 ]

  1. //使用Bulk插入的情况 [ 较快 ]
  2. #region [ 使用Bulk插入的情况 ]
  3. static void BulkToDB(DataTable dt)
  4. {
  5. Stopwatch sw = new Stopwatch();
  6. SqlConnection sqlconn = new SqlConnection("server=.;database=BulkTestDB;user=sa;password=123456;");
  7. SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlconn);
  8. bulkCopy.DestinationTableName = "BulkTestTable";
  9. bulkCopy.BatchSize = dt.Rows.Count;
  10. try
  11. {
  12. sqlconn.Open();
  13. if (dt != null && dt.Rows.Count != 0)
  14. {
  15. bulkCopy.WriteToServer(dt);
  16. }
  17. }
  18. catch (Exception ex)
  19. {
  20. Console.WriteLine(ex.Message);
  21. }
  22. finally
  23. {
  24. sqlconn.Close();
  25. if (bulkCopy != null)
  26. {
  27. bulkCopy.Close();
  28. }
  29. }
  30. }
  31. static DataTable GetTableSchema()
  32. {
  33. DataTable dt = new DataTable();
  34. dt.Columns.AddRange(new DataColumn[] {
  35. new DataColumn("Id",typeof(int)),
  36. new DataColumn("UserName",typeof(string)),
  37. new DataColumn("Pwd",typeof(string))
  38. });
  39. return dt;
  40. }
  41. static void BulkInsert()
  42. {
  43. Console.WriteLine("使用简单的Bulk插入的情况");
  44. Stopwatch sw = new Stopwatch();
  45. for (int multiply = 0; multiply < 10; multiply++)
  46. {
  47. DataTable dt = GetTableSchema();
  48. for (int count = multiply * 100; count < (multiply + 1) * 100; count++)
  49. {
  50. DataRow r = dt.NewRow();
  51. r[0] = count;
  52. r[1] = string.Format("User-{0}", count * multiply);
  53. r[2] = string.Format("Pwd-{0}", count * multiply);
  54. dt.Rows.Add(r);
  55. }
  56. sw.Start();
  57. BulkToDB(dt);
  58. sw.Stop();
  59. Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));
  60. }
  61. }
  62. #endregion
复制代码

循环插入1000条数据,每次插入100条,插入10次,服从 快了很多。

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

三.使用 简称TVPs插入数据

打开sqlserrver,实行 以下脚本:

  1. --Create Table Valued
  2. CREATE TYPE BulkUdt AS TABLE
  3. (Id int,
  4. UserName nvarchar(32),
  5. Pwd varchar(16))
复制代码

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

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

使用 简称TVPs插入数据

  1. //使用简称TVPs插入数据 [最快]
  2. #region [ 使用简称TVPs插入数据 ]
  3. static void TbaleValuedToDB(DataTable dt)
  4. {
  5. Stopwatch sw = new Stopwatch();
  6. SqlConnection sqlconn = new SqlConnection("server=.;database=BulkTestDB;user=sa;password=123456;");
  7. const string TSqlStatement =
  8. "insert into BulkTestTable (Id,UserName,Pwd)" +
  9. " SELECT nc.Id, nc.UserName,nc.Pwd" +
  10. " FROM @NewBulkTestTvp AS nc";
  11. SqlCommand cmd = new SqlCommand(TSqlStatement, sqlconn);
  12. SqlParameter catParam = cmd.Parameters.AddWithValue("@NewBulkTestTvp", dt);
  13. catParam.SqlDbType = SqlDbType.Structured;
  14. catParam.TypeName = "dbo.BulkUdt";
  15. try
  16. {
  17. sqlconn.Open();
  18. if (dt != null && dt.Rows.Count != 0)
  19. {
  20. cmd.ExecuteNonQuery();
  21. }
  22. }
  23. catch (Exception ex)
  24. {
  25. Console.WriteLine("error>" + ex.Message);
  26. }
  27. finally
  28. {
  29. sqlconn.Close();
  30. }
  31. }
  32. static void TVPsInsert()
  33. {
  34. Console.WriteLine("使用简称TVPs插入数据");
  35. Stopwatch sw = new Stopwatch();
  36. for (int multiply = 0; multiply < 10; multiply++)
  37. {
  38. DataTable dt = GetTableSchema();
  39. for (int count = multiply * 100; count < (multiply + 1) * 100; count++)
  40. {
  41. DataRow r = dt.NewRow();
  42. r[0] = count;
  43. r[1] = string.Format("User-{0}", count * multiply);
  44. r[2] = string.Format("Pwd-{0}", count * multiply);
  45. dt.Rows.Add(r);
  46. }
  47. sw.Start();
  48. TbaleValuedToDB(dt);
  49. sw.Stop();
  50. Console.WriteLine(string.Format("Elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds));
  51. }
  52. Console.ReadLine();
  53. }
  54. #endregion
复制代码

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

循环插入1000条数据,每次插入100条,插入10次,服从 是越来越慢,后面测试,将每次插入的数据量增大,会更大的表现 TPVS插入的服从 。

到此这篇关于SQL Server批量插入数据案例详解的文章就先容 到这了,更多相干 SQL Server批量插入数据内容请搜索 脚本之家从前 的文章或继续欣赏 下面的相干 文章渴望 大家以后多多支持脚本之家!


免责声明:如果侵犯了您的权益,请联系站长,我们会及时删除侵权内容,谢谢合作!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复

使用道具 举报

avatar 做我的仆人 | 2021-9-18 02:59:46 | 显示全部楼层
admin楼主该去看心理医生了!
回复

使用道具 举报

avatar 铁血_斩蛇 | 2021-9-19 16:54:45 | 显示全部楼层
终于看完了,很不错!
回复

使用道具 举报

avatar 干将发硎鞘 | 2021-9-21 20:42:38 | 显示全部楼层
突然觉得admin楼主说的很有道理,赞一个!
回复

使用道具 举报

avatar 249764701 | 2021-9-23 17:54:30 | 显示全部楼层
很有看点!
回复

使用道具 举报

avatar 失室创 | 2021-9-26 12:42:36 | 显示全部楼层
admin楼主加油,看好你哦!
回复

使用道具 举报

avatar 123457242 | 2021-9-28 02:44:43 | 显示全部楼层
顶顶更健康!
回复

使用道具 举报

avatar 0Zombies0 | 2021-9-28 14:26:37 | 显示全部楼层
admin楼主,我告诉你一个你不知道的的秘密,有一个牛逼的网站,他卖的服务器是永久的,我们的网站用 服务器都是在这家买的,你可以去试试。访问地址:http://fwq.mxswl.com
回复

使用道具 举报

avatar 蜡烛2017 | 2021-9-29 10:13:57 | 显示全部楼层
我裤子脱了,纸都准备好了,你就给我看这个?
回复

使用道具 举报

avatar 曹羁奔陈构 | 2021-9-29 10:16:46 | 显示全部楼层
态度决定一切,不错!
回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则