一次性插入上万条数据的sql

一次性插入上万条数据的写法

 1.

/// <summary>

/// DataTable批量添加(有事务)

/// </summary> /

// <param name="Table">数据源</param>

 /// <param name="Mapping">定义数据源和目标源列的关系集合</param>

/// <param name="DestinationTableName">目标表</param>

public static bool MySqlBulkCopy(DataTable Table, SqlBulkCopyColumnMapping[] Mapping, string DestinationTableName) {

bool Bool = true;

using (SqlConnection con = new SqlConnection(ConnectionString)) {

con.Open();

using (SqlTransaction Tran = con.BeginTransaction()) {

 using (SqlBulkCopy Copy = new SqlBulkCopy(con,SqlBulkCopyOptions.KeepIdentity,Tran)) {

Copy.DestinationTableName = DestinationTableName;//指定目标表

if (Mapping != null) { //如果有数据

foreach (SqlBulkCopyColumnMapping Map in Mapping) {

Copy.ColumnMappings.Add(Map); } }

try { Copy.WriteToServer(Table);//批量添加

Tran.Commit();//提交事务 }

catch { Tran.Rollback();

//回滚事务 Bool = false; } } } }

 return Bool; }

2.MSSQL使用SqlBulkCopy,传数据源，表名，列影射，我导入150万几秒完成
  /// <summary>
  /// 使用SqlBulkCopy批量插入，只限SQLServer
  /// 缺点，没有返回行数
  /// </summary>
  /// <param name="table">填充的DataTable,支持其它数据源，请看重载</param>
  /// <param name="tableName">数据库对应表名</param>
  /// <param name="columns">插入表对应的列名集合</param>
  public void SqlBulkCopyInsert(DataTable table, string tableName, string[] columns)
  {
  SqlBulkCopy sbc = new SqlBulkCopy("接连字符串");
  sbc.DestinationTableName = tableName;
  foreach (string col in columns)
  {
  sbc.ColumnMappings.Add(col, col);
  }
  sbc.WriteToServer(table);
  }

3.其它数据库，将数据查到一个datatable,往table填充数据,再在adpt.Update(table)
  /// <summary>
  /// 多行插入,Connection/Command/DataAdapter看你连接的数据库类型
  /// 进行相应的替换即可
  /// </summary>
  /// <param name="ds">填充数据后的数据集</param>
  /// <returns>受影响行数</returns>
  public int MultyInsert(DataSet ds)
  {
  int result = 0;
  IDbConnection con = new OracleConnection("连接字符串");
  con.Open();
  IDbCommand cmd = new OracleCommand();
  cmd.CommandText = "Insert into Member(UserName,Password) values(@name,@password)";
  IDbDataParameter namePar = cmd.CreateParameter();
  namePar.ParameterName = "@name";
  namePar.SourceColumn = "UserName";
  namePar.SourceVersion = DataRowVersion.Original;
  namePar.DbType = DbType.String;
  cmd.Parameters.Add(namePar);
  IDbDataParameter passPar = cmd.CreateParameter();
  passPar.ParameterName = "@pass";
  passPar.DbType = DbType.String;
  passPar.SourceColumn = "Password";
  passPar.SourceVersion = DataRowVersion.Original;
  cmd.Parameters.Add(passPar);
  IDbDataAdapter adpt = new OracleDataAdapter();
  adpt.InsertCommand = cmd;
  try
  {
  result = adpt.Update(ds);
  }
  catch (Exception)
  {

  throw;
  }
  finally
  {
  con.Close();
  }
  return result;
  }