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

     }

}

 

 

 

 

转载于:https://www.cnblogs.com/fjzhang/archive/2012/05/07/2487124.html