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