一、手动批量执行,基本上五百左右会特别慢,速度非常慢,不推荐
二、使用SqlBulkCopy,速度超级快
/// <summary> /// /// </summary> /// <param name="connectionString">目标连接字符</param> /// <param name="TableName">目标表</param> /// <param name="dt">源数据</param> private void SqlBulkCopyByDatatable(string connectionString, string TableName, DataTable dt) { using (SqlConnection conn = new SqlConnection(connectionString)) { using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction)) { try { sqlbulkcopy.DestinationTableName = TableName; sqlbulkcopy.ColumnMappings.Add("test2", "test2"); //两个参数第一个是数据库的名称、第二个是DataTable的列名 sqlbulkcopy.WriteToServer(dt); } catch (System.Exception ex) { throw ex; } } } }
使用:
DataTable dt = new DataTable(); dt.Columns.Add("test2", System.Type.GetType("System.String")); //添加行数据 for (int i = 0; i < 100; i++) { DataRow dr = dt.NewRow(); dr["test2"] = "test2"; dt.Rows.Add(dr); } qlBulkCopyByDatatable(sqlcon, "tableName", dt);
摘自:https://www.cnblogs.com/lanyubaicl/p/5863817.html