如何通过SQL中的链接服务器保存存档过程中删除身份插入等现有数据？

本文介绍了如何通过SQL中的链接服务器保存存档过程中删除身份插入等现有数据？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

我正在做数据存档从Original到Archival DB via链接服务器。

我在Archival Process之前通过脚本删除Archival DB中表格的插入/约束。为了实现相同的临时表，通过在Archival DB中通过游标传递带有Identity Insert的实际表来动态创建，临时表在删除Archival DB中的表后重命名为真实表。这样做是为了避免在通过链接服务器进行存档时插入数据问题。

这对没有数据的Archival DB很好。但是如果有数据那么带有标识插入的表的所有数据都将消失。我想在之前使用身份插入& ;;保存Archival DB中的现有数据。限制。

如果我在执行身份插入删除脚本时再次备份并恢复数据，这些表的数据将会丢失。

我尝试了什么：

以下是为了删除档案中的身份插入/约束等而执行的脚本数据库通过链接服务器工作

/ *一次性脚本，用于删除存档数据库中用于链接服务器的表格的标识插入案例* /

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

Archival

如何通过SQL中的链接服务器保存存档过程中删除身份插入等现有数据？

问题描述

推荐答案