如何从 Jet SQL/Access 2003 中的表中移动（插入和删除）最后 n 行？
我有一个每天接收公众意见的系统。每天早上启动时，我都想运行一个 VB 脚本，将最新 500 个条目之外的每个输入移动到备份表中。这是系统活动的完整档案。
我想将最初 500 行（按 k 列排序）之外的每一行从一个表移动到另一个表（将行从表“活动”插入到表“存档”中，然后从表“活动”中删除行）。
我希望能够通过单个 SQL 语句来完成此操作，但没有取得太大成功。有没有一种合理的方法可以将其作为单个（嵌套？）Jet SQL 语句来执行此操作？是否需要编写一些中间 VB 脚本来处理此操作？
提前致谢，
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,
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
看起来我可能必须做这样的事情。
这里的 500 是我想保留在系统中的行数。另一种方法是将 MsgId 列表存储在某处（在 VB 中）并从中构造第二个查询。
Looks like I might have to do something like this.
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.
为什么不以某种方式标记旧记录，使普通用户无法再查看它们呢？当您想要查询两个表中的数据等时，使用存档表将会很痛苦。
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.