Smart data archiving stored procedure

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ArchiveData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ArchiveData]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/*
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
Stored Procedure dbo.ArchiveData

Created:  04/24/2007

Description:  This stroed procedure dynamically creates a SQL statement to archive data
from a production table to an archive table.  This assumes that the structure of the source
and destination tables are identical.  The SQL statement is constructed by retrieving the
column list for the tables from syscolumns (excluding computed columns).  The columns are
then appended into a single string via a cursor over the columns result set.  This string is
used to create a SQL statement in the following format:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

INSERT INTO <destination table> (<column list>)
SELECT <column list>
FROM <source table>
WHERE <date column name> < <cut off date>

DELETE FROM <source table>
WHERE <date column name> < <cut off date>

IF @@ERROR = 0
  BEGIN
    COMMIT TRANSACTION
  END
ELSE
  BEGIN
    ROLLBACK TRANSACTION
  END

The generated SQL string is then executed via sp_executesql.

The procedure requires the following input parameters:

@SourceTable sysname - This is the table the data is archived from

@DestinationTable sysname - This is the table where the data is archived to

@CutOffInterval int - The number of days/months/years used to determine which records to archive

@CutOffType varchar(4) - The type of value specified in the @CutOffInterval variable.  This must be
                         a valid date part value for the DATEADD funtion (yy,m,d, etc)

@DateColumnName sysname - This is the name of the datetime column in the source table used to
                          determine which records are archived.  All records with a value in
                          this column less than the datetime calculated by the @CutOffInterval
                          and @CutOffType variables will be archived.

@PrintOnly bit - Indicates the procedure should generate and print the SQL commands to archive
                 but not actually execute them.  Useful if you want to customize the commands.  Note
                 that the default on this parameter is 1, so explicitly set to 0, this proc will only
                 print the SQL command, it will not execute it.

Revision History:

04/24/2007  Chris HouseInitial creation

---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------
 */CREATE PROCEDURE dbo.ArchiveData

(
@SourceTable sysname,
@DestinationTable sysname,
@CutOffInterval int,
@CutOffType varchar(4),
@DateColumnName sysname,
@PrintOnly bit = 1
)

AS

DECLARE @SQL nvarchar(4000)  -- The SQL commands that will be executes
DECLARE @ColumnList varchar(4000)  -- The list of columns in the source and destination tables
DECLARE @Column sysname  -- The current column in the cursor loop
DECLARE @CRLF nvarchar(20)  -- CRLF to make the generated SQL look pretty when it's printed
DECLARE @CutOffSQL nvarchar(500)  -- Holds a SQL command that generates the cut off date
DECLARE @CutOffSQLParamList nvarchar(100)  -- The parameter list passed into sp_executesql when generating the cut off date time
DECLARE @CutOffDate nvarchar(20)  -- The cut off date, generated by using DATEADD with the @CutOffInterval and @CutOffType parameters

-- First we build a SQL string that when executed, will give us a datetime to use as the cut off, to determine which records are archived
SET @CutOffSQL = N'SELECT @CutOffDate = CONVERT(varchar(20),DATEADD(' + @CutOffType + ', -ABS(' + CAST(@CutOffInterval AS nvarchar(10)) + '), GETDATE()),100)'
SET @CutOffSQLParamList = N'@CutOffDate nvarchar(20) OUTPUT'
EXEC sp_executesql @CutOffSQL, @CutOffSQLParamList, @CutOffDate OUTPUT

SET @CRLF = CHAR(13) + CHAR(10)
SET @ColumnList = ''

-- Now we get a cursor of all columns in the source table, excluding computed colums
DECLARE column_cursor CURSOR FAST_FORWARD
FOR
SELECT SC.name
FROM syscolumns SC
INNER JOIN sysobjects SO ON SC.id = SO.id
WHERE ((SO.name = @SourceTable) AND (SC.iscomputed = 0))
ORDER BY SC.colorder

OPEN column_cursor

-- Next we loop through the cursor and create a list of columns that will be used in the INSERT and SELECT statements
FETCH NEXT FROM column_cursor INTO @Column

WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @ColumnList = @ColumnList + QUOTENAME(@Column) + ','
    FETCH NEXT FROM column_cursor INTO @Column
  END

CLOSE column_cursor
DEALLOCATE column_cursor

-- Clean up the trailing comma on the column list
SET @ColumnList = SUBSTRING(@ColumnList,1,LEN(@ColumnList) - 1)

-- And now we build the SQL commands, complete with transaction handling
SET @SQL = 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE' + @CRLF
SET @SQL = @SQL + 'BEGIN TRANSACTION' + @CRLF
SET @SQL = @SQL + 'INSERT INTO ' + @DestinationTable + ' (' + @ColumnList + ')' + @CRLF
SET @SQL = @SQL + 'SELECT ' + @ColumnList + @CRLF
SET @SQL = @SQL + 'FROM ' + @SourceTable + @CRLF
SET @SQL = @SQL + 'WHERE ' + @DateColumnName + ' < ''' + @CutOffDate + '''' + @CRLF
SET @SQL = @SQL + 'DELETE FROM ' + @SourceTable + @CRLF
SET @SQL = @SQL + 'WHERE ' + @DateColumnName + ' < ''' + @CutOffDate + '''' + @CRLF
SET @SQL = @SQL + 'IF @@ERROR = 0' + @CRLF
SET @SQL = @SQL + '  BEGIN' + @CRLF
SET @SQL = @SQL + '    COMMIT TRANSACTION' + @CRLF
SET @SQL = @SQL + '  END' + @CRLF
SET @SQL = @SQL + 'ELSE' + @CRLF
SET @SQL = @SQL + '  BEGIN' + @CRLF
SET @SQL = @SQL + '    ROLLBACK TRANSACTION' + @CRLF
SET @SQL = @SQL + '  END' + @CRLF
PRINT @SQL

-- And finally, If @PrintOnly = 0, we execute the commands, otherwise the command string
-- will just be printed
IF @PrintOnly = 0
  BEGIN
    EXEC sp_executesql @SQL
  END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO