table archivisation

Reversable transfer records from one table to another

[mcibor]

Hi!

I have large amount of data stored in a table, most of which isn't used any more. However I cannot just delete it. So I want to create an archive table - no problem in that.

The problem comes when I want to return some record from archive to normal table. I need this record to have the same id, as it had before archivisation. How to do that? Id is auto_increment.

OK. I just tried something and don't understand it... I erased one record (id = 7) and then did query INSERT INTO table(id, test) VALUES('7', 'This is test'); And it created such record with id=7. Is it an appropriate way to do this?

And is there a non php way of moving data between tables?

I would welcome answer to at least few of these questions.
Best regards
Michal Cibor

[ergophobe]

Generally, unless you intervene, once used an auto-increment id is never reused, so it's always there for you to plug back in. So yes

1. move to archive table, storing
- archive_id (auto-increment)
- active_id (you set this yourself in your query and store it)
- field1, field etc

2. Now just do as you did - move it back and use active_id to set the id for the new record. Obviously, you will want to first check whether the record exists and decide whether it should be an update or an insert.

As for the "non-php" way, this is the sort of task that I would rarely do with PHP because it is so fast and easy to do an INSERT.. SELECT

[dev.mysql.com...]