How do I move (INSERT+DELETE) the last n rows from a table in Jet SQL / Access 2003?

Question

Hi,

I have a system that receives input from the public each day. Each morning when it starts up I want to run a VB script that moves every input beyond the latest 500 entries into a backup table. This is kind of a complete archive of the systems activity.

I want to move (INSERT row from table 'active' into table 'archive' and then DELETE row from table 'active') every row beyond the initial 500 rows (sorted by column k) from one table to another.

I was hoping to be able to do this as a single SQL statement but haven't had much success. Is there a reasonable way to do this as a single (nested?) Jet SQL statement? Will have to write some intermediate VB Script to handle this action?

Thanks in advance,

Answer 1

Looks like I might have to do something like this.

INSERT INTO ChatArchive (MsgId, MsgText, Filtered, LastFetched) SELECT MsgID, MsgText, Filtered, LastFetched FROM ChatCurrent WHERE ID <= (SELECT MAX(ID) from ChatCurrent) - 500;
DELETE FROM ChatCurrent WHERE MsgId <= (SELECT MAX(MsgId) FROM ChatArchive);

500 here being the number of rows I want to remain in the system. The alternative is to store the list of MsgIds somewhere (in VB) and construct the second query from that.

Answer 2

Why not just flag the old records in some fashion so they aren't viewable by regular users any more? Using an archive table will be a pain down the road when you want to query data in both tables, etc, etc.