sql - insert select

[Anon]

I have one table with about 86,000 records and I want to archive the data in that table once a day and keep that archive for 5 days. I setup a php script to insert into table_b select * from table_a; and cron'd wget to execute it once a day. I realized I needed a date field to expire the old stuff. So I added a data stamp field. However this broked my insert. Is the information in the date stamp field not auto generated like the a field set to auto_increment? Anyway, any guideance would be appriciated.

fyi i'm running:
mysql: 3:22:32
php: 4.0.3pl1

[Anon]

Do this instead:

using mysqldump, dump your database to a file then tar.gz that file and move it to an archive directory then delete the mysqldump'ed file.

I have been doing this for a year on my mysql site and it works great. With around 200,000 records, the archive size is around 10 megs.

[Anon]

seems i wasn't clear. I want to be able to search the archive. While what you suggested would work to archive, its not a serchable archive.

[Anon]

So does the table get reset daily and the archive get repopulated with 86k records?

If not, just setup a cron to check a timestamp field that is the date entered and delete all that are older than 5 days.

If something else is happening, you need to spend some time to really articulate your problem.

[Anon]

The data gets reset hourly actually, I'm having no problems there. The problem exists when i try to run the following sql query.

insert into table_b select * from table_a

that query works fine when the fields in both tables are exact. However when I add a timestamp field to table_b the query errors out telling me that the field counts don't match.

[Anon]

Well yea, of course. You'd have to do an exact select and insert sql to make it always work, but if you are modifying table structures, the exact select will break quickly.

Isn't there a
create table from (select * from othertable)?
I think there is....look into it.

[Anon]

Is not the purpose of a time/date stamp to stamp the time something was inserted? I want it to stamp each record, when it was inserted.

[Anon]

fine, then do a specific select like I mentioned.

insert into (a, bunch, of, fields, here, but, no, timestamp) values ( select (a, bunch, of, fields, here, but, no, timestamp) );

leaving the timestamp to be null in the insert which will cause it to timestamp now()

[Anon]

The table you insert into must have the same number of columns as the table you are selecting out of.

If yhe only difference is that your "archive" table has an extra datetime field as the last column, try this:

Insert into archive_table select *,now() from old_table;

But like Tom mentioned, this breaks when your table structure changes.