Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Stored Proc Archives rows to another table

By Farrell Thomas,

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:

         This is the source table name in the format  :  [database].[schema].[tablename]
         This is the destination table in the format     :  [database].[schema].[tablename]
         This is the date column name to check in the source table.
         This is the Purge Date. If the value in the @column_to_check column is less than this, it will be purged.
          This is the numbe of rows to purge in each transaction. You can adjust this for optimal performance.
          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.

Total article views: 322 | Views in the last 30 days: 322
Related Articles

Auto Column Check

Design Table which automatic check column status from other table


The Agent is suspect the process is idle since 500 minutes

The Agent is suspect the process is idle since 500 minutes


USING Check Constraint - Avoid halting the process and ERROR

Avoid Error and place NULL in Column when Check Constraint is violated


Locks - Blocked Processes

Where to check for blocked processes in SQL Server ??


SSIS error checking loop

Making several conditional checks to include/exclude rows from processing