游标 cursor 分批更新表记录&&while

server sql  400W的表，批量更新

while 更新比游标快

declare @rt int 
set @rt=625867
while @rt<1458873
BEGIn
     
    update  a
	set DEAL=b.DEAL,mtime='2021-03-13 09:30:46.987'
    from  pgenius..STk_MKT a WITH(NOLOCK)
   join   s37.ginagao.dbo.STK_MKT_DEAL2019 b WITH(NOLOCK)
   on a.TRADEDATE=b.TRADEDATE and a.SecCode=b.STOCKCODE
   where a.ISVALID=1 and a.DEAL is null 
   and b.rt>=@rt and b.rt<=@rt+10000

   set  @rt=@rt+10000
END

　　

---有BUG，有空再改
declare @SecCode varchar(12)  
declare @sql_cmd varchar(2000)
declare sp cursor for
select SecCode from #STK_MKT_tmp where SecCode=SecCode
open sp
fetch next from sp into @SecCode
while(@@fetch_status=0)
BEGIn
	
	set @sql_cmd= 'update  a 
 set  DEAL=b.DEAL,mtime='+'''2021-03-11 10:56:46.987'''+
 ' from  cgenius..STK_MKT a 
 left join s7.ginagao.dbo.STK_MKT_DEAL b 
 on a.tradedate=b.TRADEDATE and a.SecCode=b.STOCKCODE
 where a.DEAL is null  and ISVALID=1
 and  a.SecCode='+@SecCode

exec  ( @sql_cmd)
	fetch next from sp into @SecCode
END
close sp
deallocate sp

 

while 

 declare @a int,@b int
 set @a=1 
 while @a<22068549
 begin
     set @b=@a+1000
     insert into [tranconf_pp](S_TABNAME,PROCNAME,PARAM,UPDATEFIELD)
     select   'INDX_CLOSE_WEIGHT','DP_INDX_CLOSE_WEIGHT_2_C_INDX_CLOSE_WEIGHT',SEQ,''
    FROM DATAPOOL..INDX_CLOSE_WEIGHT with (nolock)
    where seq between @a and @b
    set @a=@b+1
    --- waitfor delay '00:03:00'
     truncate table  tranconf_pp 
 end

　　

update  backupdb..tempxwj_61
set  sort =b.id
from  backupdb..tempxwj_61 a join (
select ROW_NUMBER() over (order by enddate,inner_code ) as id,
inner_code,enddate from  backupdb..tempxwj_61) b 
on a.enddate=b.enddate and a.inner_code=b.inner_code


 declare @sumcount int
 set @sumcount=(select count(1) from backupdb..tempxwj_61)

 declare @flag int
 set @flag=1
 declare @inner_code int,@PDATE datetime
 while @flag<=@sumcount
 begin 
   select @inner_code=inner_code,@PDATE=enddate
   from backupdb..tempxwj_61 
   where sort=@flag 
   begin try   
    EXEC ANA_FND_MKT_DAY_main @inner_code,@PDATE,1
    update backupdb..tempxwj_61  set done=1 where sort=@flag
   end try
   begin catch
    update backupdb..tempxwj_61  set done=2 where sort=@flag
   end catch 

   set @flag=@flag+1 
 end