SQL Server 批量插入数据的两种方法.docx

SQL Server 批量插入数据的两种方法 在SQL Server 中插入一条数据使用Insert语句，但是如果想要批量插入一堆数据的话，循环使用Insert不仅效率低，而且会导致SQL一系统性能问题。下面介绍SQL Server支持的两种批量数据插入方法：Bulk和表值参数(Table-Valued Parameters)。运行下面的脚本，建立测试数据库和表值参数。1. -CreateDataBase2. createdatabaseBulkTestDB;3. go4. useBulkTestDB;5. go6. -CreateTable7. CreatetableBulkTestTable(8. Idintprimarykey,9. UserNamenvarchar(32),10. Pwdvarchar(16)11. go12. -CreateTableValued13. CREATETYPEBulkUdtASTABLE14. (Idint,15. UserNamenvarchar(32),16. Pwdvarchar(16)下面我们使用最简单的Insert语句来插入100万条数据，代码如下：1. Stopwatchsw=newStopwatch();2. 3. SqlConnectionsqlConn=newSqlConnection(4. ConfigurationManager.ConnectionStringsConnStr.ConnectionString);/连接数据库5. 6. SqlCommandsqlComm=newSqlCommand();7. sqlComm.CommandText=string.Format(insertintoBulkTestTable(Id,UserName,Pwd)values(p0,p1,p2);/参数化SQL8. sqlComm.Parameters.Add(p0,SqlDbType.Int);9. sqlComm.Parameters.Add(p1,SqlDbType.NVarChar);10. sqlComm.Parameters.Add(p2,SqlDbType.VarChar);11. sqlComm.CommandType=CommandType.Text;12. sqlComm.Connection=sqlConn;13. sqlConn.Open();14. try15. 16. /循环插入100万条数据，每次插入10万条，插入10次。17. for(intmultiply=0;multiply10;multiply+)18. 19. for(intcount=multiply*100000;count(multiply+1)*100000;count+)20. 21. 22. sqlComm.Parametersp0.Value=count;23. sqlComm.Parametersp1.Value=string.Format(User-0,count*multiply);24. sqlComm.Parametersp2.Value=string.Format(Pwd-0,count*multiply);25. sw.Start();26. sqlComm.ExecuteNonQuery();27. sw.Stop();28. 29. /每插入10万条数据后，显示此次插入所用时间30. Console.WriteLine(string.Format(ElapsedTimeis0Milliseconds,sw.ElapsedMilliseconds);31. 32. 33. catch(Exceptionex)34. 35. throwex;36. 37. finally38. 39. sqlConn.Close();40. 41. 42. Console.ReadLine();耗时图如下：由于运行过慢，才插入10万条就耗时72390 milliseconds，所以我就手动强行停止了。下面看一下使用Bulk插入的情况：bulk方法主要思想是通过在客户端把数据都缓存在Table中，然后利用SqlBulkCopy一次性把Table中的数据插入到数据库代码如下：1. publicstaticvoidBulkToDB(DataTabledt)2. 3. SqlConnectionsqlConn=newSqlConnection(4. ConfigurationManager.ConnectionStringsConnStr.ConnectionString);5. SqlBulkCopybulkCopy=newSqlBulkCopy(sqlConn);6. bulkCopy.DestinationTableName=BulkTestTable;7. bulkCopy.BatchSize=dt.Rows.Count;8. 9. try10. 11. sqlConn.Open();12. if(dt!=null&dt.Rows.Count!=0)13. bulkCopy.WriteToServer(dt);14. 15. catch(Exceptionex)16. 17. throwex;18. 19. finally20. 21. sqlConn.Close();22. if(bulkCopy!=null)23. bulkCopy.Close();24. 25. 26. 27. publicstaticDataTableGetTableSchema()28. 29. DataTabledt=newDataTable();30. dt.Columns.AddRange(newDataColumn31. newDataColumn(Id,typeof(int),32. newDataColumn(UserName,typeof(string),33. newDataColumn(Pwd,typeof(string);34. 35. returndt;36. 37. 38. staticvoidMain(stringargs)39. 40. Stopwatchsw=newStopwatch();41. for(intmultiply=0;multiply10;multiply+)42. 43. DataTabledt=Bulk.GetTableSchema();44. for(intcount=multiply*100000;count(multiply+1)*100000;count+)45. 46. DataRowr=dt.NewRow();47. r0=count;48. r1=string.Format(User-0,count*multiply);49. r2=string.Format(Pwd-0,count*multiply);50. dt.Rows.Add(r);51. 52. sw.Start();53. Bulk.BulkToDB(dt);54. sw.Stop();55. Console.WriteLine(string.Format(ElapsedTimeis0Milliseconds,sw.ElapsedMilliseconds);56. 57. 58. Console.ReadLine();59. 耗时图如下：可见，使用Bulk后，效率和性能明显上升。使用Insert插入10万数据耗时72390，而现在使用Bulk插入100万数据才耗时17583。最后再看看使用表值参数的效率，会另你大为惊讶的。表值参数是SQL Server 2008新特性，简称TVPs。对于表值参数不熟悉的朋友，可以参考最新的book online，我也会另外写一篇关于表值参数的博客，不过此次不对表值参数的概念做过多的介绍。言归正传，看代码：view plaincopy to clipboardprint?1. publicstaticvoidTableValuedToDB(DataTabledt)2. 3. SqlConnectionsqlConn=newSqlConnection(4. ConfigurationManager.ConnectionStringsConnStr.ConnectionString);5. conststringTSqlStatement=6. insertintoBulkTestTable(Id,UserName,Pwd)+7. SELECTnc.Id,nc.UserName,nc.Pwd+8. FROMNewBulkTestTvpASnc;9. SqlCommandcmd=newSqlCommand(TSqlStatement,sqlConn);10. SqlParametercatParam=cmd.Parameters.AddWithValue(NewBulkTestTvp,dt);11. catParam.SqlDbType=SqlDbType.Structured;12. /表值参数的名字叫BulkUdt，在上面的建立测试环境的SQL中有。13. catParam.TypeName=dbo.BulkUdt;14. try15. 16. sqlConn.Open();17. if(dt!=null&dt.Rows.Count!=0)18. 19. cmd.ExecuteNonQuery();20. 21. 22. catch(Exceptionex)23. 24. throwex;25. 26. finally27. 28. sqlConn.Close();29. 30. 31. 32. publicstaticDataTableGetTableSchema()33. 34. DataTabledt=newDataTable();35. dt.Columns.AddRange(newDataColumn36. newDataColumn(Id,typeof(int),37. newDataColumn(UserName,typeof(string),38. newDataColumn(Pwd,typeof(string);39. 40. returndt;41. 42. 43. staticvoidMain(stringargs)44. 45. Stopwatchsw=newStopwatch();46. for(intmultiply=0;multiply10;multiply+)47. 48. DataTabledt=TableValued.GetTableSchema();49. for(intcount=multiply*100000;count(multiply+1)*100000;count+)50. 51. DataRowr=dt.NewRow();52. r0=count;53. r1=string.Format(User-0,count*multiply);54. r2=string.Format(Pwd-0,count*multiply);55. dt.Rows.Add(r);56. 57. sw.Start();58. TableValued.TableValuedT