SQL Clone
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:

@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.
          
          
   

Total article views: 503 | Views in the last 30 days: 10
 
Related Articles
FORUM

Auto Column Check

Design Table which automatic check column status from other table

FORUM

The Agent is suspect the process is idle since 500 minutes

The Agent is suspect the process is idle since 500 minutes

FORUM

USING Check Constraint - Avoid halting the process and ERROR

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

FORUM

Locks - Blocked Processes

Where to check for blocked processes in SQL Server ??

FORUM

SSIS error checking loop

Making several conditional checks to include/exclude rows from processing

Tags
archive    
purge    
 
Contribute