Move TOP (x) records from one table to another with WHERE conditions

Question

I need to move the TOP (X) oldest rows from a live table into an ARCHIVE table, but the rows are not in date order.

Data will be moved on a DATE criteria and due to the fact that there are millions of records that may need to be moved over time, this will be done gradually. Therefore the queries will use a SELECT TOP and DELETE TOP.

My SQL Script so far is

INSERT INTO tblDataLoggedArchived 
SELECT TOP(1000) *
FROM tblDataLogged
WHERE fldDateTime <= @ExpiryDate 
ORDER BY fldDateTime ASC

DELETE TOP(1000) FROM tblDataLogged
WHERE fldDateTime <= @ExpiryDate
***** ORDER BY fldDateTime ASC  **** HERE's the problem
COMMIT 

However - here's the problem.

The records that are copied into the archive table have an ORDER BY clause applied to the SELECT. I need to make sure that these records are deleted from the original table - but you cannot specify an ORDER BY for the DELETE TOP query

I am by no means an SQL expert. Can someone please advise me the best way of moving the TOP X records from one table to another, using an ORDER BY parameter?

Note, the original table does not have any indexes.

Thanks

Answer 1

You can put into a CTE or derived table to delete the TOP ordered by some criteria.

Also there is no need for two statements here you can use the OUTPUT clause to do it all in one.

WITH T
     AS (SELECT TOP(1000) *
         FROM   tblDataLogged
         WHERE  fldDateTime <= @ExpiryDate
         ORDER  BY fldDateTime ASC)
DELETE FROM T
OUTPUT DELETED.*
INTO tblDataLoggedArchived