Safely trancating table?

[BOA_Dev]

I have a pending users table where all membership requests are stored. In the morning daily process I select all rows from this table and then go through each and if they meet the requirements they are inserted into the actual users table. After this process I want to empty out the pending users table.

My fear is what if after I select the rows from the pending table, some user requests a membership and their data gets entered into the table. Then after I run the process I empty the table and their request gets deleted before its ever processed!

Can anyone help me deal with this issue? The only way I can think of would be instead of truncating the entire table, I would just delete the rows I selected in the first place by doing something like:

delete from pending_users where ID = (IDs from original select statement)

but I wouldnt even know how to do that...

Thanks for any help!

 

[cjard]

put a timestamp column on the table, whose default value is the current time (so when a record is added it is timestamped)

run this query at the start (pseudocode, do whatever it takes to realise this):

Dim maxTC as DateTime = <SELECT MAX(timestamp_col) FROM table>




then run and process these applications:

SELECT * FROM table WHERE timestamp_col < @maxTC


then delete them:

DELETE FROM table WHERE timestamp_col < @maxTC


-

you can also do this with an autoincrementing number

 

[BOA_Dev]

Thanks! using an auto incrementing number should work well