new query from result?

robinjohn

Hi everyone,
the idea is that the results of a query are converted into an insert and delete query later on. Instead of building a SQL statement from the resultset's fields, is there a way to take some kind of result id and do something with their associated results?

The problem I'm (going to) have to solve is this, the boss wants an archive feature that dumps everything that falls within a certain date into an archive table where, no doubt it will never be looked at again.

The select/insert/delete statements are grand, the queries can be built up by looping through query results headers and values....I guess I'm just lazy and want an quicker solution /-:

Any comments?
cheers,
Rob.

eoghain

I'm confused, why do you need to deal with the result sets?

Why not just build your insert/delete statements with the same WHERE clause that you use to show the data? Unless your going to allow your user to select items that can be archived from the result set?

If you are just archiving everything that your select returns then just use it to do the other work.

SELECT *
  FROM my_table
 WHERE date_id < '1/1/03';

INSERT INTO my_archive
SELECT *
  FROM my_table
 WHERE date_id < '1/1/03';

DELETE FROM my_table
 WHERE date_id < '1/1/03';

Those 3 statements would work to show all items that will be archived, insert those items into the archive table, and remove them from the original table. No messing with the result sets needed.