It is a common and business requirement to backup the data. The archiving is part of back ups and most of the applications and system will be following some procedure to archive the data.
Most applications have some kind of mechanism to archive data like SQL stored procedures, statements and Database bound applications to do the archiving.
I have seen bigger and more complex statements in SQL stored procedures to copy the data into archive tables based on some conditions, say created date etc.
But, the problem starts when we add tables or change structure of some tables. We often forget to update or create archiving tables / table structures, which in turn will fail or miss out the newly created table or field data.
I was thinking to automate this process, so that, if any of the above problems arises, the archiving process will continue.
I have created a stored procedure to do the same; this does the following things as validation and execution procedure
- Check whether the archiving table exists for the original table; if not, it will create a table with the same structure of the original table
- Check whether the structure of both original and archiving table are same; if not, it will rename the old archiving table and create a new archiving table
- Check that any identity column exists in the table. This will automatically identify the identity column name
- Change the identity feature of the column of archiving table. You need to remove the identity property, otherwise, the original table data cannot be inserted as is in archiving table
- Insert the data from the original table into archiving table based on some condition
This stored procedure can be run as a SQL job and keep on running on a specified interval.
However, some enhancement in some functionality will help the procedure to be 100% fail proof, like
- As of now, to remove the identity feature (not the column), this will remove the identity column and add a column with the same name.
- Due to this, the original table identify column should be the last one. As add will add a column always last of the structure.
The complete SQL stored procedure
declare @tablename varchar(500)
declare @sql varchar(5000)
declare @idname varchar(50)
declare @tablearchive varchar(500)
--Select all the tables which you want to make in archive
declare tableCursor cursor FAST_FORWARD FOR
SELECT table_name FROM INFORMATION_SCHEMA.TABLES
where table_name
--Put your condition, if you want to filter the tables
--like '%TRN_%' and charindex('Archive',table_name) = 0 and charindex('ErrorLog',table_name) = 0
--Open the cursor and iterate till end
OPEN tableCursor
FETCH NEXT FROM tableCursor INTO @tablename WHILE @@FETCH_STATUS = 0
BEGIN
set @tablearchive = @tablename+'Archive'
--check for the table exsits, not, create it
IF not EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME= @tablearchive)
begin
SET @sql = 'select * into ' + @tablearchive +' from '+ @tablename +' where 1=2'
EXEC(@sql)
END
--check the structure is same, if not, create it
IF exists (select column_name from
INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@tablename and column_name not in (select column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@tablearchive))
begin
SET @sql = 'drop table ' + @tablearchive
EXEC(@sql)
SET @sql = 'select * into ' + @tablearchive +' from '+ @tablename +' where 1=2'
EXEC(@sql)
end
--Check if the table contains, identify column,if yes, then it should be handled in differet way
--You cannot remove the identity column property through T-SQL
--Since the structure of both tables are same, the insert fails, as it cannot insert the idenfity column
--value in the archive table
IF EXISTS(SELECT * FROM information_schema.tables WHERE table_name = @tablename AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') != 0)
BEGIN
--Select the identity column name automatically
select @idname = column_name from
information_schema.columns where
columnproperty(object_id(table_name),column_name,'isidentity')=1
AND table_name = @tablearchive
--Remove the column
SET @sql = 'ALTER TABLE ' + @tablearchive + ' DROP COLUMN ' + @idname
EXEC(@sql)
--Create the column name again (not as identity)
--archive table does require identity column
SET @sql = 'ALTER TABLE ' + @tablearchive + ' ADD ' + @idname+ ' INT'
EXEC(@sql)
END
SET @sql = 'insert into ' + @tablearchive +' select * from '+ @tablename
EXEC(@sql)
FETCH NEXT FROM tableCursor INTO @tablename
END
CLOSE tableCursor
DEALLOCATE tableCursor
Any suggestions are welcome to improve this.