时间戳

首页 > 解决方案 > 将数据归档到另一个表的动态存储过程

sql - 将数据归档到另一个表的动态存储过程

问题描述

CREATE PROCEDURE dbo.ArchiveData
    (@CutOffDate datetime = NULL)
AS
BEGIN
    SET NOCOUNT ON

    IF @CutOffDate IS NULL 
    BEGIN
        SET @CutOffDate = DATEADD(mm, -6, CURRENT_TIMESTAMP)
    END
    ELSE
    BEGIN
        IF @CutOffDate > DATEADD(mm, -3, CURRENT_TIMESTAMP)
        BEGIN
            RAISERROR ('Cannot delete orders from last three months', 16, 1)
            RETURN -1
        END
    END

    BEGIN TRAN
        INSERT INTO Archive.dbo.Orders
            SELECT * 
            FROM dbo.Orders
            WHERE OrderDate < @CutOffDate

        IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRAN
            RAISERROR ('Error occurred while copying data to Archive.dbo.Orders', 16, 1)
            RETURN -1
        END

        INSERT INTO Archive.dbo.OrderDetails
            SELECT *
            FROM dbo.OrderDetails
            WHERE OrderID IN (SELECT OrderID
                              FROM dbo.Orders
                              WHERE OrderDate < @CutOffDate)

        IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRAN
            RAISERROR ('Error occurred while copying data to  Archive.dbo.OrderDetails', 16, 1)
            RETURN -1
        END

        DELETE dbo.OrderDetails
        WHERE OrderID IN (SELECT OrderID
                          FROM dbo.Orders
                          WHERE OrderDate < @CutOffDate)

        IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRAN
            RAISERROR ('Error occurred while deleting data from dbo.OrderDetails', 16, 1)
            RETURN -1
        END

        DELETE dbo.Orders
        WHERE OrderDate < @CutOffDate

        IF @@ERROR <> 0
        BEGIN
            ROLLBACK TRAN
            RAISERROR ('Error occurred while deleting data from dbo.Orders',  16, 1)
            RETURN -1
        END

       IF @@TRANCOUNT > 0
       BEGIN
           COMMIT TRAN
           RETURN 0
       END
END

如何在一个存储过程中一次归档所有表以归档表？我需要在普通表中保留过去 30 天，但将所有过去的数据存档到存档中。我需要使用 If Else 条件吗？我需要动态获取所有表并动态归档表。我需要为此使用动态 SQL 查询。

标签： sqlsql-servertsqlazure-sql-databasedynamic-sql

解决方案

如果您的存档表结构与普通表相同，那么您可以通过您的表及其关系顺序进行循环

您可以借助此链接按关系顺序获取表格：

如何按依赖顺序列出表（基于外键）？

将表插入临时表并为每个表生成插入查询

DECLARE @T AS TABLE(ID INT IDENTITY(1,1), TableName NVARCHAR(100))
-- INSERT INTO @T the tables with relation order
while(exists select * from @T)
BEGIN
    DECLARE @ID = (SELECT TOP 1 ID FROM @T ORDER BY ID)
    DECLARE @TableName = (SELECT TOP 1 TableName FROM @T WHERE ID=@ID)
    EXEC('INSERT INTO Archive.dbo.' + @TableName + ' SELECT * FROM ' + @TableName)
    DELETE FROM @T WHERE ID = @ID
END

您可以通过相同的循环但以相反的顺序从普通表中删除数据“SELECT TOP 1 ID FROM @T ORDER BY ID DESC”

推荐阅读