SQLServer 数据批量更新注意 数据量大小

最近，数据库有写数据需要运维一下，批量更新，数据量大约在60W左右

一开始偷懒了，想着图省事，批量更新，看看效果，一次更新3W条数据

drop table #temp
select top 30000 ID  into #temp  from SAP_ChargeBillSync_201909 with(nolock)
where  EndTime<'2019-09-12' and  DataState in('0','3') and SAPEnabled='0'

update  SAP_ChargeBillSync_201909 set DataState='1',DataStateReason='SAPEnabled为0或空',LastModifier='man20190921',LastModifyTime=GETDATE()
where id in(select * from #temp)

 结果问题就来了，对表加锁时间太长，锁表超时，影响业务数据写入了

所以更换了一种写法，update数据的尽可能的逐条执行，这样不会锁表。

declare @ID varchar(36)
declare @sqltxt varchar(250)

declare curtbls cursor for 
	select top 50000 ID  from SAP_ChargeBillSync_201909 with(nolock)
	where  EndTime<'2019-09-12' and  DataState in('0','3') and SAPEnabled='0'

	open curtbls 
	fetch next from curtbls into @ID 

	while @@FETCH_STATUS =0
	begin 
	select @sqltxt ='update  SAP_ChargeBillSync_201909 set DataState=''1'',DataStateReason=''SAPEnabled为0或空'',LastModifier=''man20190921'',LastModifyTime=GETDATE()
	                 where id ='+''''+@ID +'''';
    exec (@sqltxt);
	fetch next from curtbls into @ID
	end 
	close curtbls
	deallocate curtbls