Technical Article

Stored Proc Archives rows to another table

,

This stored procedure will Archive and then Purge rows from one table to another.

It uses the 'DELETE OUTPUT' statement to delete and move the rows to the destination table, so its pretty fast.

You can set the number of rows that will be purged in each transaction loop to find the optimal size.

It can be set to only run for a certain amount of minutes. For example,  you can setup an agent job that will run at 7:00 pm, and only run it for 30 minutes. 

If the destination table does not exist, it will be created.

The parameters passed are:

@sourcetablename
         This is the source table name in the format  :  [database].[schema].[tablename]
@desttablename  
         This is the destination table in the format     :  [database].[schema].[tablename]
@column_to_check
         This is the date column name to check in the source table.
@purge_date
         This is the Purge Date. If the value in the @column_to_check column is less than this, it will be purged.
@batch_size 
          This is the numbe of rows to purge in each transaction. You can adjust this for optimal performance.
@minutes_to_process
          This is the minutes that the process will run. The process will exit after this amount of minutes have elasped.
example usage:
exec usp_archive_rows  @sourceTABLENAME = '[ftest].[dbo].[Table_1]',   
                           @desttablename = '[ftest_log].[dbo].[arch_table_1]', 
                           @column_to_check = 'logdate',
                           @PURGE_DATE = '2014-04-01 00:00:00.000', 
                           @BATCH_SIZE = 500, 
                           @MINUTES_TO_PROCESS = 15
This will arhcive the rows in [ftest].[dbo].[Table_1] into [ftest_log].[dbo].[arch_table_1]. 
It will check the column 'logdate' in the sourcetable for rows with the date is less than 2014-01-01 00:00:00.000.
It will purge 500 rows in each transaction loop.
It will run for 15 minutes and then exit.
          
          
   
-- =============================================
-- Author:Farrell Thomas
-- Create date: 3/16/2017
-- Description:This will archive rows from one table to another table
-- =============================================


-- sample usage
-- EXEC usp_archive_rows  @sourceTABLENAME = '[ftest].[dbo].[Table_1]',    --[database].[schema].[tablename] format
                         --@desttablename = '[ftest_log].[dbo].[arch_table_1]', 
 --@column_to_check = 'logdate',
 --@PURGE_DATE = '2014-04-01 00:00:00.000', 
 --@BATCH_SIZE = 500, 
 --@MINUTES_TO_PROCESS = 5

 
CREATE PROCEDURE [dbo].[usp_archive_rows]

@sourcetablename varchar(200) ,                                  -- source table to purge
@desttablename   varchar(200),                                   -- dest table
@column_to_check varchar(200),                                   -- date column name to check in source table
@purge_date varchar(50)  = '1990-01-01 00:00:00.000',            -- default to 1990 in case no date was specified than nothing should be purged 
@batch_size int = 50 ,                                           -- default to 50 for batch size
@minutes_to_process int = 5                                      -- default to 5 minutes to run
AS
BEGIN

DECLARE @SQLSTR NVARCHAR(500)
DECLARE @KEEP_PROCESSING INT = 0
DEClARE @STARTTIME DATETIME = GETDATE()
DECLARE @NUMMINUTES INTEGER
DECLARE @CURRTIME DATETIME = GETDATE()
DECLARE @TABLEROWS INTEGER = 0
DECLARE @TOTALDEL INTEGER = 0

--- count number of rows to delete

SET @SQLSTR = 'Set @Dynamicint = (select count(*) from ' + @sourcetablename + ' where ' + @column_to_check + ' < ' + '''' + @purge_date + '''' + ')'

EXEC sp_executesql
    @SQLSTR
    ,N'@Dynamicint int OUTPUT'
    ,@TABLEROWS OUTPUT

SET ROWCOUNT @batch_size       -- set the rowcount to the passed batch size
SET NOCOUNT ON                 -- DONT DISPLAY NUMBER OF ROWS MESSAGES

PRINT 'Source Table : ' + @sourcetablename
PRINT 'Destination  : ' + @desttablename
PRINT 'Purge Date   : ' + @purge_date + ' <-- rows with ' + @column_to_check + ' less than this will be moved and purged'

PRINT 'TOTAL ROWS TO PROCESS = ' + CONVERT(VARCHAR(10),@TABLEROWS)

------this will create the destination table if it dosent exist

IF OBJECT_ID(@desttablename, N'U') IS NULL
BEGIN
  SET @SQLSTR = 'Select * into ' + @desttablename + ' from ' + @sourcetablename + ' where 1 = 0'
  EXEC (@sqlstr)
  PRINT 'Created destination Table : ' + @desttablename
END

-- Construct sql delete statement
       
   SET @SQLSTR = 'DELETE ' + @sourcetablename + ' OUTPUT DELETED.* INTO ' + @desttablename + ' where ' + @column_to_check + ' < ' + '''' + @purge_date + ''''

-----------------------------------
--- main loop
-----------------------------------

--PROCESS THE ROWS WHILE KEEP_PROCESSING = 0

WHILE @KEEP_PROCESSING = 0

BEGIN

       BEGIN TRANSACTION

   EXEC(@sqlstr)   -- execute the delete statement

   SET @TOTALDEL = @TOTALDEL + @@ROWCOUNT

       commit

PRINT 'Total Processed so far = ' + CONVERT(VARCHAR(10),@TOTALDEL)

--check if all rows processed

    IF  @TOTALDEL >= @TABLEROWS 
      BEGIN
          PRINT 'All Rows Processed - EXITING JOB'
  PRINT 'TOTAL ROWS DELETED = ' + CONVERT(VARCHAR(10),@TOTALDEL)
          SET @KEEP_PROCESSING = 1

  -- commit last transaction if still open

  if @@TRANCOUNT > 0
  begin
    commit
  end

      END

   ---- check if number of minutes to run has expired.

   SET @CURRTIME = GETDATE()
   SET @NUMMINUTES =  datediff(MINUTE, @STARTTIME,@CURRTIME)
    
   IF  @NUMMINUTES > @minutes_to_process
   BEGIN
          PRINT 'RUN TIME EXCEDDED - EXITING JOB'
  PRINT 'TOTAL ROWS DELETED = ' + CONVERT(VARCHAR(10),@TOTALDEL)
          SET @KEEP_PROCESSING = 1

   -- commit last transaction if still open

  if @@TRANCOUNT > 0
  begin
    commit
  end
   END

---------------------------------------------
---  end main loop
---------------------------------------------

END



END

Rate

3 (2)

Share

Share

Rate

3 (2)