How to keep existing data on removing identity insert etc in archival process via linked server in SQL ?

See more:

SQL

How to keep existing data  on removing identity insert etc in Archival process via Linked Server  in Sql ?

I am doing data Archival from Original to Archival DB via Linked Server.

I am removing identity Insert/constraints of tables in Archival DB via a script before Archival Process. To achieve the same temporary tables are created dynamically by passing the real tables with Identity Insert in Archival DB via cursor and temporary tables are renamed to the real tables after dropping the tables in Archival DB . This is done to avoid insertion of data issue while doing archival via Linked server.

This is fine for Archival DB without data. But if data is there then all data of tables with identity insert will be gone. I want to keep the existing data in Archival DB there before with identity insert & constraints.

If i take the back up and restore the data again on executing the identity insert removal script data of such tables will be lost.

What I have tried:

Below is the script executed for removing Identity Insert/constraints etc in Archival DB to work via Linked Server
/* One Time Script for removing the Identity Insert Off for Tables in Archival DB for Linked Server Case*/

BEGIN

DECLARE @Query NVARCHAR(MAX)
DECLARE @TableName NVARCHAR(MAX)
declare @DISABLECONSTRAINT Nvarchar(MAX)
declare @ENABLECONSTRAINT Nvarchar(MAX)
BEGIN TRY                
BEGIN TRAN               

--Created a  Cursor named IDENTITYINSERTOFF to fetch all Tables IN Archival DB and Pass the Table 
--one by one to Parameter @TableName and Create Temporary Table dynamically to each corrsp. Table
DECLARE IDENTITYINSERTOFF CURSOR FOR 
select TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
ORDER BY ORDINAL_POSITION

OPEN IDENTITYINSERTOFF 
Fetch next from IDENTITYINSERTOFF into 
@TableName 

--Ended 

WHILE @@FETCH_STATUS = 0  
BEGIN
            --SET DISABLECONSTRAINT FOR THE SAME
            SET @DISABLECONSTRAINT = N''
            SELECT 
                  @DISABLECONSTRAINT = @DISABLECONSTRAINT + 'ALTER TABLE ' +  OBJECT_SCHEMA_NAME(parent_object_id) +
                  '.[' + OBJECT_NAME(parent_object_id) + 
                  '] DROP CONSTRAINT ' + name + N';'
            FROM sys.foreign_keys
            WHERE referenced_object_id = object_id(@TableName)
            --PRINT @DISABLECONSTRAINT
            EXECUTE sp_executeSQL @DISABLECONSTRAINT
            --Dynamically Create Temporary Table 
            SET @Query = N'CREATE TABLE [TMP_'+ @TableName + N'](';

            --Set Columns to  character Max length  based on data types present 
            SELECT @Query= @Query + QUOTENAME(COLUMN_NAME) + N' '+ 
            CASE 
                        WHEN DATA_TYPE IN ('numeric', 'decimal') THEN DATA_TYPE + N' (' + CAST(NUMERIC_PRECISION AS NVARCHAR(10)) + N', ' + CAST(NUMERIC_SCALE AS  NVARCHAR(10)) + N')'
                        WHEN DATA_TYPE IN ('varchar', 'nvarchar') AND CHARACTER_MAXIMUM_LENGTH = -1 THEN DATA_TYPE + N'(MAX)'
                        WHEN DATA_TYPE IN ('varchar', 'nvarchar') AND CHARACTER_MAXIMUM_LENGTH >= 1 THEN DATA_TYPE + N'(' + CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(10)) + N')'
                        WHEN DATA_TYPE IN ('char') THEN DATA_TYPE + N' (' + CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(10)) + N')'
                        WHEN DATA_TYPE IN ('datetime', 'date', 'time', 'tinyint', 'smallint', 'int', 'bigint') THEN DATA_TYPE
                        WHEN DATA_TYPE IN ('money', 'bit') THEN DATA_TYPE
                        ELSE DATA_TYPE
            end + N','
            FROM  INFORMATION_SCHEMA.COLUMNS 
            WHERE TABLE_NAME = @TableName
            ORDER BY ORDINAL_POSITION;
             --Ended
            -- Close the Brace of the Dynamic Create Table Syntax
            SET @Query = LEFT(@Query , LEN(@Query) - 1)
            SET @Query = @Query + N')';
            --Ended

            --PRINT @Query

            -- Execute the Create Temporary Table Query
            EXECUTE sp_executesql @Query
            --Ended

            --Drop Original Table in Archival DB
            SET @Query =N'DROP TABLE '+ QUOTENAME(@TableName)
           -- PRINT @Query
            EXECUTE sp_executesql @Query
            --Ended

            --Rename the Temporary Table to Original Table  in Archival DB
            SET @Query = N'sp_rename '+ N'[TMP_'+@TableName+ N'],'+ QUOTENAME(@TableName)
            --PRINT @Query
            --PRINT ''
            EXECUTE sp_executesql @Query
            --Ended
            --Fetch the next value from the Cursor titled IDENTITYINSERTOFF
            Fetch next from IDENTITYINSERTOFF into 
            @TableName
            --Ended
END
--Commit Transaction
COMMIT TRAN 
--Ended
--Close and Deallocate Cursor named IDENTITYINSERTOFF
CLOSE IDENTITYINSERTOFF  
DEALLOCATE IDENTITYINSERTOFF 
--Ended



END TRY 
               
BEGIN CATCH  
    DECLARE @errMsg AS VARCHAR(MAX)       
    SET @errMsg = ERROR_MESSAGE() 
    --PRINT @errMsg
    --Close and Deallocate Cursor named IDENTITYINSERTOFF
    CLOSE IDENTITYINSERTOFF  
    DEALLOCATE IDENTITYINSERTOFF
     --Ended 
      --RollBack the Transaction if there is any Issue 
      ROLLBACK TRAN
      --Ended
                  
END CATCH  

END