CODEWITHSUNDEEP
sql-servert-sql
haag1
haag1

Reputation: 352

Proper Query for Archiving Tables in SQL Server

I have two queries written up, I am wondering which is best practice for archiving tables if my team chooses to go the route of using a query to do the archiving. We are looking for the solution that is most efficient but doesn't compromise any data integrity. We are looking to keep only the last 18 months in some of our biggest tables, no idea as of yet on how often we'll be running this archive, but I would imagine once a month.

Option 1: Without a loop

DECLARE @18Months datetime = DATEADD(m, -18, CAST(GETDATE() AS Date))

BEGIN TRY
BEGIN TRANSACTION t1
    INSERT INTO tblOneArchive
        SELECT * FROM tblOne WHERE dateField < @18Months
    DELETE FROM tblOne WHERE datefield < @18Months
COMMIT TRANSACTION t1
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION t1
END CATCH

Option 2: With a loop

DECLARE @id INT
SELECT @id = MIN(ID) FROM tblOne WHERE dateField < @18Months
WHILE @id IS NOT NULL
BEGIN
    BEGIN TRANSACTION t2
    INSERT INTO tblOneArchive
        SELECT * FROM tblOne WHERE ID = @id
    DELETE FROM tblOne WHERE ID = @id
    COMMIT TRANSACTION t2
    SELECT @id = MIN(ID) FROM tblOne WHERE ID > @id AND dateField < @18Months
END

If Option 2 is best, how should I handle the try catch? Thanks.

Upvotes: 1

Views: 374

Answers (1)

j.v.
j.v.

Reputation: 997

Option 1 with using fast order delete. Check it out here https://www.brentozar.com/archive/2018/04/how-to-delete-just-some-rows-from-a-really-big-table/

Upvotes: 1

Related Questions