I need to loop list of tables from INFORMATION_SCHEMA.TABLES and for each table_name output i need to create a seperate new table with data.
Ex : SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where table_Name in
('EMP','DEPT','SAL')
My Procedure should drop older table names if they exists and create three new tables with systemdate appeneded from above results
drop BKP_<Table_Name>_* //*Drop Old tables which are one week older with
systemdate <=(sysdate-7))
select * into BKP_<Table_Name>_<SYSTEMDATE> FROM <Table_Name> ;
could you please help
Example of the logic.
Some adjustments still can be done, like error handling, better logging or extra parameter to pass - a table list. But can be a start point for further steps
CREATE PROCEDURE usp_BackupTable
@RetentionDays int = 7
AS
DECLARE @_tbl sysname, @_schema sysname, @_sql_Backup VARCHAR(400), @_sql_Drop VARCHAR(400);
-- first part: create backups of listed tables
DECLARE ct CURSOR FOR
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME IN ('ETLSettings') AND TABLE_TYPE = 'BASE TABLE'
OPEN ct
FETCH NEXT FROM ct INTO @_schema, @_tbl
WHILE @@FETCH_STATUS = 0
BEGIN
SET @_sql_Backup = 'SELECT * INTO ['+@_schema+'].BKP_'+@_tbl+'_'+CONVERT(varchar(20), GETDATE(), 112)+' FROM ['+@_schema+'].['+@_tbl+'] ;'
EXEC (@_sql_Backup)
PRINT 'Backup of table ' + @_tbl + ' has been created'
FETCH NEXT FROM ct INTO @_schema, @_tbl
END
CLOSE ct
DEALLOCATE ct
-- second part: cleanup of backups
DECLARE ct CURSOR FOR
SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'BKP%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' AND TABLE_TYPE = 'BASE TABLE'
AND TRY_CAST(RIGHT(TABLE_NAME, 8) AS DATETIME ) < CAST(GETDATE()-@RetentionDays AS DATE)
OPEN ct
FETCH NEXT FROM ct INTO @_schema, @_tbl
WHILE @@FETCH_STATUS = 0
BEGIN
SET @_sql_Drop = 'DROP TABLE ['+@_schema+'].['+@_tbl+'] ;'
EXEC (@_sql_Drop)
PRINT 'Table ' + @_tbl + ' has been deleted'
FETCH NEXT FROM ct INTO @_schema, @_tbl
END
CLOSE ct
DEALLOCATE ct