Deleting records while inserting same records into new table

[fchan]

Hi,

I have a small asp app that allows our HR group to maintain job postings (add, update, delete) on our public web site. Our HR Rep would like a list of the deleted postings.

How can I delete records from one table while inserting the same records into another table? I know how to do this with one record at a time, but I'm not sure how to do this with multiple records.

To delete the postings, the HR Rep checks a checkbox that contains the ID of the posting. The delete processing asp page takes the ID or ID's from the submitted form and places the ID's in an array that is used to build my "Delete" SQL statement. Then I use the Execute command (connectionObject.Execute) to run the SQL statement.

Any idea on how I can use a similar approach to insert the records?

Thanks

 

[JuanitaC]

This isn't tested at all, but you could try something like this before you delete the records:

SQL = " INSERT INTO table1 (column, column, column..)"
SQL = SQL & " SELECT column, column, column FROM table2"
SQL = SQL & " WHERE id IN (" & deleteIDList & &quot"

Where deleteIDList is a comma delimited string of the IDs that you want to delete.