SQL Archive Script

Question

I'm trying to archive records from a table in a database to an identical table in an archive database. I need to be able to do an insert for all records with a date greater than three years ago, and then delete those rows. However, this table has millions of records which are live, so I want to run this in a loop of roughly 100 to 1000 chunks at a time. So far my stored procedure does the entire insert statement, then a delete statement (in a transaction) with essentially the same WHERE clause as the insert statement. My WHILE loop is looking for the oldest date in the table to determine when the loop is completed. Some of this seems pretty inefficient. Is there a way I can do an insert and delete on the chunk of records without having to look them up twice in the same loop execution? Is there a better way to determine when the WHILE statement is completed? Running MS SQL Server 2000.

This is my current Procedure (ISAdminDB is the main DB, ISArchive is the archive DB):

    WHILE ( (SELECT MIN( [MyTable].[DateTime]) FROM  [ISAdminDB].[dbo].[MyTable]) < DATEADD(d, -(3 * 365), GetDate()))
BEGIN

INSERT INTO [ISArchive].[dbo].[MyTable] 
(<Fields>)
SELECT TOP 1000 (<Fields>)
FROM  [ISAdminDB].[dbo].[MyTable]
WHERE 
   [MyTable].[DateTime] < DATEADD(d, -(3 * 365), GetDate())
AND  UniqueID in (SELECT TOP 1000 UniqueID  FROM  [ISAdminDB].[dbo].[MyTable] ORDER BY [MyTable].[DateTime] ASC )

BEGIN TRAN
DELETE FROM  [ISAdminDB].[dbo].[MyTable]
WHERE   [MyTable].[DateTime] < DATEADD(d, -(3 * 365), GetDate()) 
AND  (UniqueID in (SELECT TOP 1000 UniqueID FROM  [ISAdminDB].[dbo].[MyTable] ORDER BY [MyTable].[DateTime] ASC))
COMMIT

END

Answer 1

Firstly, you're deleting records earlier than a specific date, 3 years ago. You don't care what order they're deleted in, you just need to keep deleting them until there aren't any left. You can also speed things up by using a temporary table to store the IDs, and by storing the cut-off date in a variable and repeatedly referring to it.

So now we have :

DECLARE @NextIDs TABLE(UniqueID int primary key)
DECLARE @ThreeYearsAgo datetime
SELECT @ThreeYearsAgo = DATEADD(d, -(3 * 365), GetDate())

WHILE EXISTS(SELECT 1 FROM [ISAdminDB].[dbo].[MyTable] WHERE [MyTable].[DateTime] < @ThreeYearsAgo)
BEGIN 
    BEGIN TRAN 

    INSERT INTO @NextIDs(UniqueID)
        SELECT TOP 1000 UniqueID FROM [ISAdminDB].[dbo].[MyTable] WHERE [MyTable].[DateTime] < @ThreeYearsAgo

    INSERT INTO [ISArchive].[dbo].[MyTable] (<Fields>) 
        SELECT (<Fields>) 
        FROM  [ISAdminDB].[dbo].[MyTable] AS a
        INNER JOIN @NextIDs AS b ON a.UniqueID = b.UniqueID

    DELETE [ISAdminDB].[dbo].[MyTable]
    FROM  [ISAdminDB].[dbo].[MyTable] 
    INNER JOIN @NextIDs AS b ON a.UniqueID = b.UniqueID 

    DELETE FROM @NextIDs

    COMMIT TRAN
END