关于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();
    }
}