Dynamic stored procedure to archive data to another tables

Question

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

How can I archive all the tables at once in one stored procedure to archive tables? I need to keep past 30 days in the normal tables but archive all past data into archive. Do I need to use If Else condition? I need to dynamically get all the tables and dynamically archive tables. I need to use dynamic SQL querying for this.

Saeed Taran · Answer 1 · 2020-03-20T18:38:27.983

if your archive tables structure are the same as normal tables then you can do it with a loop through you tables with their relation order

you can get the tables by their relation order with help of this link:

How to list tables in their dependency order (based on foreign keys)?

insert the tables into a temp table and generate an insert query for each table

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

you can delete data from normal tables after archiving them by the same loop but in reverse order "SELECT TOP 1 ID FROM @T ORDER BY ID DESC"

Dynamic stored procedure to archive data to another tables

1 Answers1