/* One Time Script for keep exisiting data and proceed Archival with Linked Server*/ BEGIN DECLARE @Query NVARCHAR(MAX) --Pass/Set Archival DB(Existing Archival DB) DECLARE @ArchivalDB NVARCHAR(MAX) SET @ArchivalDB='FTS_DIB_ARCH3' --Pass/Set Temp Archival DB(Temp Archival DB where existing data is stored temporarily on restoring) DECLARE @TempArchivalDB NVARCHAR(MAX) SET @TempArchivalDB='FTS_DIB_ARCH2' --Pass/Set Schema Name DECLARE @TABLE_SCHEMA NVARCHAR(MAX) SET @TABLE_SCHEMA= 'dbo' DECLARE @TableName NVARCHAR(MAX) Declare @STRDELETE NVarchar(4000) BEGIN TRY --Created a Cursor named ARCHIVALDBINSERT to fetch all Tables IN Archival DB --and Pass the Table one by one to Parameter @TableName and insert into Archival DB from temp Archival DB DECLARE ARCHIVALDBINSERT CURSOR FOR select name from SYS.TABLES OPEN ARCHIVALDBINSERT Fetch next from ARCHIVALDBINSERT into @TableName --Ended WHILE @@FETCH_STATUS = 0 BEGIN --PRINT 123 --delete from Archival DB to which data is inserted from Temp Archival DB initially before inserting to avoid duplicate insertion SET @STRDELETE='delete from '+ @ArchivalDB +'.dbo.'+@TableName print @STRDELETE --Execute the deletion from Archival DB to which data is inserted from Temp Archival DB initially before inserting to avoid duplicate insertion EXECUTE sp_executesql @STRDELETE --Insert into Existing Archive DB with Schema Name passed above SET @Query = N'INSERT INTO ' + @ArchivalDB+'.'+ @TABLE_SCHEMA+'.'+ @TableName + '('; --Pass Columns for each columns of the Table in Archival DB with timestamp datatype ignored SELECT @Query= @Query + case when DATA_TYPE='timestamp' then '' else QUOTENAME(COLUMN_NAME)+ N',' end FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName ORDER BY ORDINAL_POSITION; --Ended --Trim the Comma at the end of Insert Query SET @Query= LEFT(@Query, NULLIF(LEN(@Query)-1,-1)) -- Close the Brace of the Dynamic Create Table Syntax SET @Query = @Query + N')'; --Select from Temp Archive DB (Tables) with data' SELECT @Query = @Query + N'SELECT '; --Pass Columns for each columns of the Table in Temp Archival DB with timestamp datatype ignored SELECT @Query= @Query + case when DATA_TYPE='timestamp' then '' else QUOTENAME(COLUMN_NAME)+ N',' end FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName ORDER BY ORDINAL_POSITION;; --Ended --Trim the Comma at the end of Select from Query SET @Query= LEFT(@Query, NULLIF(LEN(@Query)-1,-1)) --Select from Temp Archive DB with Schema Name passed above (where existing data after restoring is stored temporarily) SET @Query= + @Query + N' FROM ' + @TempArchivalDB+'.'+ @TABLE_SCHEMA+'.'+ @TableName ; --PRINT 'K' --To Print the Insert into Select From Query PRINT @Query -- Execute the Insert into Select From Query(Saving data to Existing Archival DB from Termp Archival Tables) EXECUTE sp_executesql @Query --Ended --Fetch the next value from the Cursor titled ARCHIVALDBINSERT Fetch next from ARCHIVALDBINSERT into @TableName --Ended END --Ended --Close and Deallocate Cursor named ARCHIVALDBINSERT CLOSE ARCHIVALDBINSERT DEALLOCATE ARCHIVALDBINSERT --Ended END TRY BEGIN CATCH DECLARE @errMsg AS VARCHAR(MAX) SET @errMsg = ERROR_MESSAGE() --PRINT @errMsg --Close and Deallocate Cursor named ARCHIVALDBINSERT CLOSE ARCHIVALDBINSERT DEALLOCATE ARCHIVALDBINSERT --Ended --Ended END CATCH END
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)