SQL Server 批量插入数据

　　有时候需要在数据库中批量插入数据，使用普通循环插入效率很低，插入10000条数据耗时接近一分钟，这是无法忍受的。还在SQL Server在.NET下提供了批量插入的方法SqlBulkCopy，执行效率瞬间从屌丝逆袭成了高富帅。

　　方法一、普通循环批量插入：

SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=123456");
SqlCommand cmd = new SqlCommand();
conn.Open();
cmd.Connection = conn;
int insertCount = 1000;
for (int i = 1; i <= insertCount; i++) {
    cmd.CommandText = String.Format("insert into users(name,age) values('小明{0}',{0})", i);
    cmd.ExecuteNonQuery();
}
conn.Close();
conn.Dispose();

　　方法二、使用SqlBulkCopy批量插入：

DataTable dt = new DataTable();
dt.Columns.Add("name", typeof(string));
dt.Columns.Add("age", typeof(int));
int insertCount = 1000;
for (int i = 1; i <= insertCount; i++) {
    DataRow r = dt.NewRow();
    r["name"] = "小红" + i.ToString();
    r["age"] = i;
    dt.Rows.Add(r);
}

using (SqlBulkCopy bulk = new SqlBulkCopy("Data Source=(local);Initial Catalog=test;Persist Security Info=True;User ID=sa;Password=123456")) {
    bulk.BatchSize = insertCount;
    bulk.DestinationTableName = "users";
    bulk.ColumnMappings.Add("name", "name");
    bulk.ColumnMappings.Add("age", "age");
    bulk.WriteToServer(dt);
    bulk.Close();
}

　　方法三、数据库客户端遍历执行插入：

declare @i int
set @i=1
while @i<=1000 begin
    insert into users(name,age) values('小明',1)
    set @i=@i+1    
end

　　经测试，三种方法执行效率与不同数据量的关系如下表所示：

	0.1w	1w	10w	100w	500w
方法一	0.289s	24.562s	>4分钟	太大	太大
方法二	0.013s	0.067s	0.633s	6s	27s
方法三	0s	2s	18s	>4分钟	太大

 

 

 

 

　　从表可知，使用SqlBulkCopy批量插入数据效率可能是普通插入的几百倍。