SQL MOVE Records to another table

Question

I have a number of functions which MOVE records from one table to another (generally for a form of archiving the data) and wondered if there was a "best practice" for doing this, or a more efficient method than I am currently using.

At the moment, I am running something like:

INSERT INTO archive_table
SELECT [ROWID], [COL1], [COL2]
FROM live_table
WHERE <criteria>

DELETE FROM live_table
WHERE [ROWID] IN
(
   SELECT [ROWID] FROM archive_table
)

This is also throwing up a warning on the SQL performance software that the query may cause index suppression and performance degradation; due to a SCAN being performed, rather than a SEEK.

Worth adding that the archive_table is an exact copy of the live_table, with the exception that we have removed the identity and primary key off of the [ROWID] column and that this table is not used within the 'live' environment, other than having the old data inserted, as described.

[edit]

Would seem that the answer from Alex provides a really simple resolution to this; the comment about using a trigger doesn't resolve the issue in this instance as the event happens a number of days later and the criteria is dependant on events during that period.

DELETE 
FROM live_table 
OUTPUT DELETED.* INTO archive_table
WHERE <criteria>

Answer 1

If you have to move large number of records from one table to another, i suggest you check the possibility to partition your "active table". Each time, you copy data from one (or more) partitions to the "achieve table" and drop those partitions. It will be much faster than delete records from an "online" table.