如何将方括号设置为SQL中动态归档脚本中设置的列？ [英] How to set square braces to the columns set in the dynamic archival script in SQL?
本文介绍了如何将方括号设置为SQL中动态归档脚本中设置的列？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我想将方括号设置为sql中动态归档脚本中设置的列。
我正在获取源列列表和目标列列表
我想拿着方括号同时取桌存档
I want to set square braces to the columns set in the dynamic archival script in sql.
I am fetching the source column list and destination column list
I want to embrace it with square braces while fetching tables for archival
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
我的尝试：
What I have tried:
/****** 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
推荐答案
在指定的表中找不到指定的列名。例外很清楚。要么你的列名错了，要么你的表名错了。
编辑=========== =======
在任何人对这个答案进行投票之前，请注意OP在我回答后显着改变了他的问题。
The specified column name wasn't found in the specified table. The exception is pretty clear. Either you got the column name wrong, or you got the table name wrong.
EDIT ==================
Before anyone down-votes this answer, be aware that the OP significantly changed his question after I answered it.
这篇关于如何将方括号设置为SQL中动态归档脚本中设置的列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
