关于sql server批量插入与更新两种解决方案

.游标方式
 DECLARE @Data NVARCHAR(max)
 SET @Data='1,tanw;2,keenboy'   --Id,Name
 
 DECLARE @dataItem NVARCHAR(100)
 DECLARE data_cursor CURSOR FOR (SELECT * FROM split(@Data,';'))
 OPEN data_cursor
 FETCH NEXT FROM data_cursor INTO @dataItem   
 WHILE @@FETCH_STATUS=0
 BEGIN
 DECLARE @Id INT
 DECLARE @Name NVARCHAR(50)
 
 DECLARE dataItem_cursor CURSOR FOR (SELECT * FROM split(@dataItem,','))
 OPEN dataItem_cursor   
 FETCH NEXT FROM dataItem_cursor INTO @Id
 FETCH NEXT FROM dataItem_cursor INTO @Name
 CLOSE dataItem_cursor
 DEALLOCATE dataItem_cursor
 
 /*
   在这里做逻辑处理，插入或更新操作 ...
 */
 END
 
 CLOSE data_cursor
 DEALLOCATE data_cursor

 

 

.While方式
 DECLARE @Data NVARCHAR(max)
 SET @Data='tanw,keenboy'   --Id,Name
 
 DECLARE @Temp TABLE
 (
    Id INT IDENTITY(1,1),
    Name  NVARCHAR(50)
 )
 DECLARE @Id INT
 DECLARE @Name NVARCHAR(50)
 DECLARE @Results NVARCHAR(MAX) SET @Results=''
 INSERT INTO @Temp SELECT (SELECT * FROM split(@Data,';'))
 
 WHILE EXISTS(SELECT * FROM @Temp)
 BEGIN
     SELECT TOP 1 @Id=Id,@Name=Name from @Temp
     DELETE FROM @Temp where [id] = @Id
     SET @Results=@Results+@Name+','
    
     /*
    
         在这里做逻辑处理，插入或更新操作 ...
    
     */
 END
 SELECT @Results

 

 

 

 //---------下面的方式比较适合----------//

 

 

 

BCP方式:

/// <summary>

/// 大批量插入数据(2000每批次)

/// 已采用整体事物控制

/// </summary>

/// <param name="connString">数据库链接字符串</param>

/// <param name="tableName">数据库服务器上目标表名</param>

/// <param name="dt">含有和目标数据库表结构完全一致(所包含的字段名完全一致即可)的DataTable</param>

public static void BulkCopy(string connString, string tableName, DataTable dt)

{

    using (SqlConnection conn = new SqlConnection(connString))

    {

    conn.Open();

 

    using (SqlTransaction transaction = conn.BeginTransaction())

    {

        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction))

        {

        bulkCopy.BatchSize = 2000;

        bulkCopy.BulkCopyTimeout = _CommandTimeOut;

        bulkCopy.DestinationTableName = tableName;

 

        try

        {

            foreach (DataColumn col in dt.Columns)

            {

            bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);

 

            }

            bulkCopy.WriteToServer(dt);

            transaction.Commit();

        }

        catch (Exception ex)

        {

            transaction.Rollback();

            throw ex;

        }

        finally

        {

            conn.Close();

        }

        }

    }

    }

}

 

 

 

 

 

SqlDataAdapter:

/// <summary>

/// 批量更新数据(每批次5000)

/// </summary>

/// <param name="connString">数据库链接字符串</param>

/// <param name="table"></param>

public static void Update(string connString, DataTable table)

{

    SqlConnection conn = new SqlConnection(connString);

    SqlCommand comm = conn.CreateCommand();

    comm.CommandTimeout = _CommandTimeOut;

    comm.CommandType = CommandType.Text;

    SqlDataAdapter adapter = new SqlDataAdapter(comm);

    SqlCommandBuilder commandBulider = new SqlCommandBuilder(adapter);

    commandBulider.ConflictOption = ConflictOption.OverwriteChanges;

    try

    {

    conn.Open();

    //设置批量更新的每次处理条数

    adapter.UpdateBatchSize = 5000;

    adapter.SelectCommand.Transaction = conn.BeginTransaction();/////////////////开始事务  

    if (table.ExtendedProperties["SQL"] != null)

    {

        adapter.SelectCommand.CommandText = table.ExtendedProperties["SQL"].ToString();

    }

    adapter.Update(table);

    adapter.SelectCommand.Transaction.Commit();/////提交事务

    }

    catch (Exception ex)

    {

    if (adapter.SelectCommand != null && adapter.SelectCommand.Transaction != null)

    {

        adapter.SelectCommand.Transaction.Rollback();

    }

    throw ex;

    }

    finally

    {

    conn.Close();

    conn.Dispose();

    }

}