SQL Server 存储过程定时转存数据

 CREATE PROCEDURE [dbo].[sp_MES_JTMSLS]      
 --创建存储过程     
as   
  
  
BEGIN         
BEGIN TRAN Tran_MoveData    --开始事务         
DECLARE @tran_error int;         
   
declare @DQSJ datetime     
set @DQSJ=GETDATE()          
SET @tran_error = 0;             
BEGIN TRY                  
   
 insert into     
JTSSJLDH(DJGUID,JTBH,DQCD,SDCD,ZCD,CJSJ)    
select NEWID()                    
,JTBH                   
,DQCD                   
,SDCD                    
,ZCD          
,CJSJ                     
from JTMSSSJLH with (nolock) where DATEDIFF(HOUR,cjsj,@DQSJ)>=24 --转存数据      
    

declare @perCount int; 
set @perCount = 50000;--每次删除数据量
while 1=1 --循环删除
begin
    delete top(@perCount) from JTMSSSJLH where DATEDIFF(HOUR,cjsj,@DQSJ)>=24  --删除原数    
    if(isnull(@@rowcount,0)<=0) break;--影响行数为0跳出循环
end

END TRY         
BEGIN CATCH             
SET @tran_error = @tran_error + 1        
PRINT ERROR_MESSAGE()    
RETURN    
END CATCH         
IF(@tran_error > 0)         
 BEGIN             
  ROLLBACK TRAN;--回滚         
 END         
ELSE         
 BEGIN             
 COMMIT TRAN;--提交         
 END     
 END