How to pass data to a temporary table and pass to real table with in a DB in SQL via a generic script?

See more:

SQL-Server

How to pass data to a temporary table and pass to real table with in a DB in Sql via a generic script?

I want to pass lost data to the existing DB. Actually I did was restoring the back up to a new db and pass data from it to the exisiting DB.i can't restore to existing DB directly as I need to remove identity insert afterwards via a script to archive data via a linked server. But how can i pass data creating a temporary table,passing data to the same and then truncate those tables afterwards.

What I have tried:

My script to transfer data from one DB to another is as below:

/* 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