游标 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

  

posted @ 2021-03-11 11:53  yongqi-911  阅读(90)  评论(0编辑  收藏  举报