SQLServer 中怎样批量插入数据

131次阅读

共计 4288 个字符，预计需要花费 11 分钟才能阅读完成。

SQLServer 中怎样批量插入数据，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。

运行下面的脚本，建立测试数据库和表值参数。

、代码如下:
–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 –Create Table Valued CREATE TYPE BulkUdt AS TABLE (Id int, UserName nvarchar(32), Pwd varchar(16))

下面我们使用最简单的 Insert 语句来插入 100 万条数据，代码如下：

Stopwatch sw = new Stopwatch(); SqlConnection sqlConn = new SqlConnection( ConfigurationManager.ConnectionStrings[ ConnStr].ConnectionString);// 连接数据库 SqlCommand sqlComm = new SqlCommand(); sqlComm.CommandText = string.Format( insert into BulkTestTable(Id,UserName,Pwd)values(@p0,@p1,@p2) // 参数化 SQL sqlComm.Parameters.Add(@p0 , SqlDbType.Int); sqlComm.Parameters.Add(@p1 , SqlDbType.NVarChar); sqlComm.Parameters.Add(@p2 , SqlDbType.VarChar); sqlComm.CommandType = CommandType.Text; sqlComm.Connection = sqlConn; sqlConn.Open(); try { // 循环插入 100 万条数据，每次插入 10 万条，插入 10 次。for (int multiply = 0; multiply multiply++) {for (int count = multiply * 100000; count (multiply + 1) * 100000; count++) {sqlComm.Parameters[ @p0].Value = count; sqlComm.Parameters[@p1].Value = string.Format(User-{0} , count * multiply); sqlComm.Parameters[@p2].Value = string.Format(Pwd-{0} , count * multiply); sw.Start(); sqlComm.ExecuteNonQuery(); sw.Stop();} // 每插入 10 万条数据后，显示此次插入所用时间 Console.WriteLine(string.Format( Elapsed Time is {0} Milliseconds , sw.ElapsedMilliseconds)); } } catch (Exception ex) {throw ex;} finally {sqlConn.Close(); } Console.ReadLine();

由于运行过慢，才插入 10 万条就耗时 72390 milliseconds，所以我就手动强行停止了。下面看一下使用 Bulk 插入的情况：bulk 方法主要思想是通过在客户端把数据都缓存在 Table 中，然后利用 SqlBulkCopy 一次性把 Table 中的数据插入到数据库代码如下：复制代码代码如下:
public static void BulkToDB(DataTable dt) {SqlConnection sqlConn = new SqlConnection( ConfigurationManager.ConnectionStrings[ ConnStr].ConnectionString); 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) {throw ex;} finally {sqlConn.Close(); if (bulkCopy != null) bulkCopy.Close();} } public 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 Main(string[] args) {Stopwatch sw = new Stopwatch(); for (int multiply = 0; multiply multiply++) {DataTable dt = Bulk.GetTableSchema(); for (int count = multiply * 100000; count (multiply + 1) * 100000; 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(); Bulk.BulkToDB(dt); sw.Stop(); Console.WriteLine(string.Format( Elapsed Time is {0} Milliseconds , sw.ElapsedMilliseconds)); } Console.ReadLine();}

耗时图如下：可见，使用 Bulk 后，效率和性能明显上升。使用 Insert 插入 10 万数据耗时 72390，而现在使用 Bulk 插入 100 万数据才耗时 17583。最后再看看使用表值参数的效率，会另你大为惊讶的。表值参数是 SQL Server 2008 新特性，简称 TVPs。对于表值参数不熟悉的朋友，可以参考最新的 book online，我也会另外写一篇关于表值参数的博客，不过此次不对表值参数的概念做过多的介绍。言归正传，看代码：复制代码代码如下:
public static void TableValuedToDB(DataTable dt) {SqlConnection sqlConn = new SqlConnection( ConfigurationManager.ConnectionStrings[ ConnStr].ConnectionString); 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; // 表值参数的名字叫 BulkUdt，在上面的建立测试环境的 SQL 中有。catParam.TypeName = dbo.BulkUdt try {sqlConn.Open(); if (dt != null dt.Rows.Count != 0) {cmd.ExecuteNonQuery(); } } catch (Exception ex) {throw ex;} finally {sqlConn.Close(); } } public 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 Main(string[] args) {Stopwatch sw = new Stopwatch(); for (int multiply = 0; multiply multiply++) {DataTable dt = TableValued.GetTableSchema(); for (int count = multiply * 100000; count (multiply + 1) * 100000; 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(); TableValued.TableValuedToDB(dt); sw.Stop(); Console.WriteLine(string.Format( Elapsed Time is {0} Milliseconds , sw.ElapsedMilliseconds)); } Console.ReadLine();}

关于 SQLServer 中怎样批量插入数据问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注丸趣 TV 行业资讯频道了解更多相关知识。

正文完