SQL Server 批量插入数据的两种方法
在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
- --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)");
- 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
- {
-
- for (int multiply = 0; multiply < 10; 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();
- }
-
- 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 < 10; 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;
-
- 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 < 10; 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();
- }
耗时图如下:
比Bulk还快5秒。
FROM:http://blog.csdn.net/tjvictor/archive/2009/07/18/4360030.aspx
PS:以上让Kevin yan本人想到了之前批量写入ORACLE数据库的情况,很怀怀疑SQL2008的TVPS是否就是跟它学来的呢?
以下为使用ODP.NET批量导入数据示例代码
004 | /// <param name="tableName">表名称</param> |
005 | /// <param name="columnRowData">键-值存储的批量数据:键是列名称,值是该列对应的数据集合</param> |
006 | /// <returns></returns> |
007 | public int BatchInsert( string tableName, Dictionary< string , object []> columnRowData) |
009 | if ( string .IsNullOrEmpty(tableName)) |
011 | throw new ArgumentNullException( "tableName" , "必须指定批量插入的表名称" ); |
014 | if (columnRowData == null || columnRowData.Count < 1) |
016 | throw new ArgumentException( "必须指定批量插入的字段名称" , "columnRowData" ); |
020 | string [] dbColumns = columnRowData.Keys.ToArray(); |
021 | StringBuilder sbCmdText = new StringBuilder(); |
022 | if (columnRowData.Count > 0) |
025 | sbCmdText.AppendFormat( "INSERT INTO {1} (" , tableName); |
026 | sbCmdText.Append( string .Join( "," , dbColumns)); |
027 | sbCmdText.Append( ") VALUES (" ); |
028 | sbCmdText.Append( ":" + string .Join( ", :" , dbColumns)); |
029 | sbCmdText.Append( ") " ); |
031 | using (OracleConnection conn = new OracleConnection( "数据库连接字符信息" )) |
033 | using (OracleCommand cmd = _oraDbConn.CreateCommand()) |
036 | cmd.ArrayBindCount = columnRowData.Values.First().Length; |
037 | cmd.BindByName = true ; |
038 | cmd.CommandType = CommandType.Text; |
039 | cmd.CommandText = sbCmdText.ToString(); |
040 | cmd.CommandTimeout = 600; |
042 | OracleParameter oraParam; |
043 | List<IDbDataParameter> cacher = new List<IDbDataParameter>(); |
044 | OracleDbType dbType = OracleDbType.Object; |
045 | foreach ( string colName in dbColumns) |
047 | dbType = this .GetOracleDbType(columnRowData[colName][0]); |
048 | oraParam = new OracleParameter(colName, dbType); |
049 | oraParam.Direction = ParameterDirection.Input; |
050 | oraParam.OracleDbTypeEx = dbType; |
052 | oraParam.Value = columnRowData[colName]; |
053 | cmd.Parameters.Add(oraParam); |
057 | var trans = conn.BeginTransaction(); |
060 | cmd.Transaction = trans; |
061 | iResult = cmd.ExecuteNonQuery(); |
064 | catch (Exception dbex) |
079 | /// <param name="tableName">表名称</param> |
080 | /// <param name="keyColumName">主键列名称</param> |
081 | /// <param name="columnRowData">键-值存储的批量数据:键是列名称,值是该列对应的数据集合</param> |
082 | /// <returns></returns> |
083 | public int BatchUpdate( string tableName, string keyColumName, Dictionary< string , object []> columnRowData) |
085 | if ( string .IsNullOrEmpty(tableName)) |
087 | throw new ArgumentNullException( "tableName" , "必须指定批量插入的表名称" ); |
090 | if ( string .IsNullOrEmpty(tableName)) |
092 | throw new ArgumentNullException( "keyColumName" , "必须指定批量插入表的主键列名称" ); |
095 | if (columnRowData == null || columnRowData.Count < 1) |
097 | throw new ArgumentException( "必须指定批量插入的字段名称" , "columnRowData" ); |
101 | string [] dbColumns = columnRowData.Keys.ToArray(); |
102 | StringBuilder sbCmdText = new StringBuilder(); |
103 | if (columnRowData.Count > 0) |
106 | sbCmdText.AppendFormat( "update {0} set " , tableName); |
107 | foreach (var col in dbColumns) |
109 | if (keyColumName.Equals(col,StringComparison.OrdinalIgnoreCase)) |
113 | sbCmdText.AppendFormat( "{0} = :{0} ," , col); |
115 | sbCmdText.Remove(sbCmdText.Length - 1, 1); |
116 | sbCmdText.AppendFormat( " where {0} = :{0}" ,keyColumName); |
118 | using (OracleConnection conn = new OracleConnection( "数据库连接字符信息" )) |
120 | using (OracleCommand cmd = _oraDbConn.CreateCommand()) |
123 | cmd.ArrayBindCount = columnRowData.Values.First().Length; |
124 | cmd.BindByName = true ; |
125 | cmd.CommandType = CommandType.Text; |
126 | cmd.CommandText = sbCmdText.ToString(); |
127 | cmd.CommandTimeout = 600; |
129 | OracleParameter oraParam; |
130 | List<IDbDataParameter> cacher = new List<IDbDataParameter>(); |
131 | OracleDbType dbType = OracleDbType.Object; |
132 | foreach ( string colName in dbColumns) |
134 | dbType = this .GetOracleDbType(columnRowData[colName][0]); |
135 | oraParam = new OracleParameter(colName, dbType); |
136 | oraParam.Direction = ParameterDirection.Input; |
137 | oraParam.OracleDbTypeEx = dbType; |
139 | oraParam.Value = columnRowData[colName]; |
140 | cmd.Parameters.Add(oraParam); |
144 | var trans = conn.BeginTransaction(); |
147 | cmd.Transaction = trans; |
148 | iResult = cmd.ExecuteNonQuery(); |
151 | catch (Exception dbex) |
165 | /// 根据数据类型获取OracleDbType |
167 | /// <param name="value"></param> |
168 | /// <returns></returns> |
169 | private OracleDbType GetOracleDbType( object value) |
171 | OracleDbType dataType = OracleDbType.Object; |
174 | dataType = OracleDbType.Varchar2; |
176 | else if (value is DateTime) |
178 | dataType = OracleDbType.TimeStamp; |
180 | else if (value is int || value is short ) |
182 | dataType = OracleDbType.Int32; |
184 | else if (value is long ) |
186 | dataType = OracleDbType.Int64; |
188 | else if (value is decimal || value is double ) |
190 | dataType = OracleDbType.Decimal; |
192 | else if (value is Guid) |
194 | dataType = OracleDbType.Varchar2; |
196 | else if (value is bool || value is Boolean) |
198 | dataType = OracleDbType.Byte; |
200 | else if (value is byte []) |
202 | dataType = OracleDbType.Blob; |
204 | else if (value is char ) |
206 | dataType = OracleDbType.Char; |