.游标方式
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(); } }