How can i insert/ select into a table and delete copied records from original table?

Question

I have a table ActivityTable and i want to move some records to a Archive table and then delete those moved records from the main table ie the ActivityTable.

I am doing the following

 INSERT INTO ArchiveTable
   ([Id]  ,
   [Name]  ,
   [Key]  ,
   [RegDate]  ,
SELECT top 1000
   [Id]  ,
   [Name]  ,
   [Key]  ,
   [RegDate]  ,       
   FROM ActivityTable
   WHERE registrationDate < cast(cast(dateadd(day, -10, getutcdate()) as Date) as DateTime) 


    DELETE
    FROM ActivityTable
    WHERE registrationDate <  cast(cast(dateadd(day, -90, getutcdate()) as Date) as DateTime)   

How can i be sure that the top 1000 that have been selected will the the exact 1000 that are deleted from the Activity Table ?

Answer 1

You can use a common unique identifier and transaction control.

This will ensure that all of the 1000 records you insert and select into ArchiveTable are the ones you delete from ActivityTable.

BEGIN TRANSACTION;

-- Create a temporary table to hold the IDs

CREATE TABLE #TempActivityIds (Id INT);

-- Insert IDs for the top 1000 records into the temp table

INSERT INTO #TempActivityIds (Id)
SELECT TOP 1000 [Id]
FROM ActivityTable
WHERE registrationDate < CAST(CAST(DATEADD(day, -10, GETUTCDATE()) AS DATE) AS DATETIME);

-- Next, insert the records into the ArchiveTable using the IDs from the temp table

INSERT INTO ArchiveTable ([Id], [Name], [Key], [RegDate])
SELECT [Id], [Name], [Key], [RegDate]
FROM ActivityTable
WHERE [Id] IN (SELECT [Id] FROM #TempActivityIds);

-- Delete records from the ActivityTable using the IDs from the temp table

DELETE FROM ActivityTable
WHERE [Id] IN (SELECT [Id] FROM #TempActivityIds);

-- Finally, drop the temp table

DROP TABLE #TempActivityIds;

COMMIT TRANSACTION;

Hope this helps!