"Moving" Records to another Table (Copy then Delete)

[ddot02]

I'm a newbie, so I’m sorry if sound ignorant

How to make the ASP code to copy the records with a request value from a table into the another table and then deleting the row/s from the 1st table?

I made 2 Tables in SQL 2000 for a membership purpose of my web.
I use the 1st one to store the data’s from new members who just complete registration. But I’m not going to give the access directly after the registration. I want to make a confirmation first before give them a full access.
The 2nd Table is for the confirmed members with full access to my web. The 2 tables are identical one another, the only difference is I add a field for members ID in the 2nd table (for the confirmed members) which is not exist in the 1st table. That field/column is the Primary key of the 2nd table, I set identity (auto numbers) into it.

My problem is, after I get a confirmation from the new members, I must move them (their records/data) from the 1st table (unconfirmed members) to the 2nd table (confirmed members), so they can access whole of my site. It because of the User Name & Password validation will look up into this 2nd table. And I want to do it in asp instead of doing it directly in SQL 2000.

I have made an asp page to show the list (with all the records) of unconfirmed members from the 1stTable. I made their Name shown as links (clickcable), so when I click on one of the members Name, the page will be redirected to another asp page that will do the action to "move" the specific member I clicked from the 1st table to the 2nd one. I only know, first i must copy the records from the 1st table to the 2nd table, and then delete the row from the 1st table.

In the SQL side we just need to make this statement:

INSERT INTO 2ndTable (Field1, Field2, Field3, Field4, Field5)
SELECT Field1,Field2,Field3,Field4,Field5 FROM 1stTable
DELETE 1stTable WHERE Field1 = ' .....'

That's all.... But how to do it in ASP?

Any help will be appreciated...
Thanks before everything...

Heddy

 

[lovejaeeun]

Can you just add a True/False field in Table1 called 'Confirmed' ? Then you don't even need Table2.

 

[ddot02]

Good Idea.. but i must set a DEFAULT for that true/false field right? and then just need to add more condition in the password validation page...

I'll try it...

Thanks a lot for the idea

 

[lovejaeeun]

Exactly.

Just set the default to FALSE. Then, when the account is confirmed, do an UPDATE on the record and change the FALSE to TRUE.

And your done!

 

[ddot02]

Ok.. It's done here...

I will try the something like 'copy and delete' later.. not for this purpose ofcourse, because the way you show me is so much more efficient, but maybe for any other purpose in the furture...

Thanks again..