Как установить квадратные скобки для столбцов, заданных в скрипте динамического архивирования в SQL?

---

Я хочу установить квадратные скобки для столбцов, заданных в динамическом архивном скрипте в sql.

Я извлекаю список исходных столбцов и список целевых столбцов

Я хочу обнять его квадратными скобками при получении таблиц для архивирования

Linked Server Details Configured        

DB Server- NESTIT-319-E\MSSQL          

Linked Server created- Test

Live DB- NWPS_DIB_LIVE_ARCHIVAL 


Archival DB Server- NTP-228
 
Archival DB- NWPSCS_Archival_DB_2018

Что я уже пробовал:

/****** Object:  StoredProcedure [dbo].[DIB_ARCHIVE]    Script Date: 11/03/2015 19:15:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
exec  [DIB_ARCHIVE] 'FTSEDGE_UAT','FTSEDGE_ARC','05-07-2015','23-11-2014'
exec  [DIB_ARCHIVE] 'FTSEDGE_Live','[10.240.176.79].[UAEFTS_ARC1]','15-07-2015','24-08-2012'
*/              
ALTER PROCEDURE [dbo].[DIB_ARCHIVE]
(                    
  @LIVEDB         NVarchar(100)                  
, @ARCHIVEDB      Varchar(100)                  
, @ArchiveDate    VARCHAR(10)               
, @ArchiveAgeDate VARCHAR(10)                 
)                    
AS                    

SET XACT_ABORT ON;

DECLARE @TableName Varchar(200)                
Declare @DateField Varchar(50)                
Declare @TableType char(1)                
Declare @STRIDENTITY NVarchar(4000)                
Declare @STRINSERT NVarchar(4000)                
Declare @STRDELETE NVarchar(4000)                
declare @SourceColumnList varchar(MAX)
declare @DestinationColumnList varchar(MAX)
declare @STRUSE Nvarchar(100)                
declare @DISABLECONSTRAINT Nvarchar(1000) declare @ENABLECONSTRAINT Nvarchar(1000) Declare @Archive_LogID INT Declare @NumRowsChanged INT                  

SET @DISABLECONSTRAINT=@LIVEDB+'.dbo.sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"'                  
EXECUTE sp_executesql @DISABLECONSTRAINT SET @DISABLECONSTRAINT=@ARCHIVEDB+'.dbo.sp_MSforeachtable @command1="ALTER TABLE ? NOCHECK CONSTRAINT ALL"'                  
EXECUTE sp_executesql @DISABLECONSTRAINT                  

set @STRUSE='USE '+@LIVEDB                
EXECUTE sp_executesql @STRUSE                  


---Archive LOg-------------                  
INSERT INTO  Archive_Log  (LiveDBName,ArchivalDBName,Archival_Date,ArchivalStartTime)                  
VALUES (@LIVEDB,@ARCHIVEDB,getdate(),getdate())                  
SET @Archive_LogID= SCOPE_IDENTITY() ---Archive LOg-------------                  

BEGIN TRY                
	BEGIN TRAN                  

	IF CURSOR_STATUS('global','ARCHIVE')>=-1                
	BEGIN                
	DEALLOCATE ARCHIVE                
	END                 

	DECLARE ARCHIVE CURSOR FOR 
	SELECT TableName,DateField,TableType FROM dbo.Archive_config 

		
	OPEN ARCHIVE fetch next from ARCHIVE into @TableName, @DateField,@TableType                  

	WHILE @@FETCH_STATUS = 0                
	BEGIN                  

		SET @STRIDENTITY=''                    
		set @SourceColumnList=''                   
		set @DestinationColumnList=''
		set @STRINSERT=''
		
		select @SourceColumnList = @SourceColumnList + ',' + case when DATA_TYPE = 'xml' then 'convert(varchar(max),'+column_name+')' else column_name end   
		from information_schema.columns  
		where table_name = @TableName  and DATA_TYPE not in ('timestamp')  

		select @DestinationColumnList= @DestinationColumnList+ ',' + column_name 
		from information_schema.columns  
		where table_name = @TableName  and DATA_TYPE not in ('timestamp')                  

		set  @SourceColumnList= RIGHT(@SourceColumnList,Len(@SourceColumnList)-1)                  
		set  @DestinationColumnList= RIGHT(@DestinationColumnList,Len(@DestinationColumnList)-1)
								
		IF(@TableType='M') --Master Table                  
		BEGIN     
		
			                  
			SET @STRDELETE='delete from '+ @ARCHIVEDB +'.dbo.'+@TableName                     
			EXECUTE sp_executesql @STRDELETE                  

			SET @NumRowsChanged=@@ROWCOUNT                  
			INSERT INTO Archive_LogDtls (Archive_LogID,TableName,Rowsaffected,[Date],[Description])                  
			VALUES (@Archive_LogID,@TableName,@NumRowsChanged,GETDATE(),'Delete from Table(Master Table) : ' +@TableName +', Archival DB  : '+ @ARCHIVEDB )                  
			
			--select @SourceColumnList = @SourceColumnList + ',' + case when DATA_TYPE = 'xml' then 'convert(varchar(max),'+column_name+')' else column_name end   
			--from information_schema.columns  
			--where table_name = @TableName  and DATA_TYPE not in ('timestamp')  
			
			--select @DestinationColumnList= @DestinationColumnList+ ',' + column_name 
			--from information_schema.columns  
			--where table_name = @TableName  and DATA_TYPE not in ('timestamp')                  
									
			--set  @SourceColumnList= RIGHT(@SourceColumnList,Len(@SourceColumnList)-1)                  
			--set  @DestinationColumnList= RIGHT(@DestinationColumnList,Len(@DestinationColumnList)-1)
			
			SET @STRINSERT=@STRIDENTITY+'INSERT INTO '+ @ARCHIVEDB +'.dbo.'+ @TableName +'('+ @DestinationColumnList + ')                  
			SELECT '+ @SourceColumnList + ' FROM  '+ @LIVEDB +'.dbo.'+@TableName        								                          
			EXECUTE sp_executesql @STRINSERT                  
			
			SET @NumRowsChanged=@@ROWCOUNT                  
			INSERT INTO Archive_LogDtls (Archive_LogID,TableName,Rowsaffected,[Date],[Description])                  
			VALUES (@Archive_LogID,@TableName,@NumRowsChanged,GETDATE(),'Insert Into Table(Master Table) : ' +@TableName +', Archival DB  : '+ @ARCHIVEDB )                  
			
		END                  
		ELSE IF(@TableType='T') --Transaction Table                
		BEGIN                  
			
			--select @SourceColumnList = @SourceColumnList + ',' + case when DATA_TYPE = 'xml' then 'convert(varchar(max),'+column_name+')' else column_name end   
			--from information_schema.columns  
			--where table_name = @TableName  and DATA_TYPE not in ('timestamp')  
			
			--select @DestinationColumnList= @DestinationColumnList+ ',' + column_name 
			--from information_schema.columns  
			--where table_name = @TableName  and DATA_TYPE not in ('timestamp')                  
									
			--set  @SourceColumnList= RIGHT(@SourceColumnList,Len(@SourceColumnList)-1)                  
			--set  @DestinationColumnList= RIGHT(@DestinationColumnList,Len(@DestinationColumnList)-1)               
			
			SET @STRINSERT=@STRIDENTITY+'INSERT INTO '+ @ARCHIVEDB +'.dbo.'+ @TableName +'('+ @DestinationColumnList + ')                  
			SELECT '+ @SourceColumnList + ' FROM  '+ @LIVEDB +'.dbo.'+@TableName + '                   
			WHERE '+ @DateField + '                  
			< CONVERT(DATETIME,'''+@ArchiveAgeDate+''',103)+ 1'                   
			
			--print @STRINSERT
			EXECUTE sp_executesql @STRINSERT                  
			
			SET @NumRowsChanged=@@ROWCOUNT                  
			INSERT INTO Archive_LogDtls (Archive_LogID,TableName,Rowsaffected,[Date],[Description])                  
			VALUES (@Archive_LogID,@TableName,@NumRowsChanged,GETDATE(),'Insert Into Table(Transaction Table) : ' +@TableName +' , Archival DB  : '+ @ARCHIVEDB )                  
													
			SET @STRDELETE='delete from '+ @LIVEDB +'.dbo.'+@TableName + '                  
			WHERE '+ @DateField + '                  
			< CONVERT(DATETIME,'''+@ArchiveAgeDate+''',103)+ 1'                 
			
			--print @STRINSERT
			EXECUTE sp_executesql @STRDELETE                  

			SET @NumRowsChanged=@@ROWCOUNT                  
			INSERT INTO Archive_LogDtls (Archive_LogID,TableName,Rowsaffected,[Date],[Description])                  
			VALUES (@Archive_LogID,@TableName,@NumRowsChanged,GETDATE(),'Delete from Table(Transaction Table) : ' +@TableName +' ,Live DB : '+ @LIVEDB )                  
			
		END

		FETCH NEXT                
		from ARCHIVE into @TableName, @DateField,@TableType 
	
	END CLOSE ARCHIVE DEALLOCATE ARCHIVE COMMIT TRAN                  

	---Archive LOg-------------                  

	UPDATE Archive_Log SET ArchivalEndTime=GETDATE(),Status='Successfully Completed '                  
	WHERE  Archive_LogID=@Archive_LogID                  

	---Archive LOg-------------                 

	PRINT 'Successfully Completed'                  
END TRY                

BEGIN CATCH                
	ROLLBACK TRAN                  

	UPDATE Archive_Log SET ArchivalEndTime=GETDATE(),Status='Archive Script Failed at Table : '+  @TableName +' , Error Message: ' + ERROR_MESSAGE()                   
	WHERE  Archive_LogID=@Archive_LogID                  

	PRINT 'Archive Script Failed at Table : '+  @TableName +' , Error Message: ' + ERROR_MESSAGE()             

END CATCH                  

SET @ENABLECONSTRAINT=@LIVEDB+'.dbo.sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL"'                  
EXECUTE sp_executesql @ENABLECONSTRAINT 

SET @ENABLECONSTRAINT=@ARCHIVEDB+'.dbo.sp_MSforeachtable @command1="ALTER TABLE ? CHECK CONSTRAINT ALL"'            
EXECUTE sp_executesql @ENABLECONSTRAINT 

---