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
M JenningsPosted Feb 19, 2021, 2:18 PM
Nice article Jayachandran. Very useful for the right situations. In my circumstance, and upstream vendor changed the primary key on a table and we need to support the change without losing the data. Your code is the perfect fit for that scenario. Archive table --> Empty source table --> Apply change to Source --> Refill Source Table From Archive --> Done.One minor tweak because you suggested being open to improvements. The order of the columns can change when the IDentity column is replaced. Insert into ... Select * will no longer work in the scenario, or worse will insert but with the data in the incorrect columns. To correct for the changing column order, I suggest dynamically obtain the field names for the insert. ... --Create and execute the insert from source into archive --Columns may have been re-ordered so calculate the column list for select and insert using destination archive as basis DECLARE @SQLInsertList varchar(4000); SELECT @SQLInsertList = '' + STUFF (( SELECT ', [' + name + ']' FROM sys.columns c WHERE c.object_id = OBJECT_ID(@TableArchive) FOR XML PATH('')), 1, 1, '') + ''; SET @SQL = 'insert into ' + @TableArchive + '(' + @SQLInsertList + ')' + ' select ' + @SQLInsertList + ' from '+ @TableName; EXEC(@SQL) ...