Consecutive numbering WITHOUT Identity

Hi,

I'mtrying to insert records from one table into another table. Thedestination table has a ROWID field which cannot be an identity key,but needs to 'act like' an identity key and have its value populatedwith (Max(ROWID) + 1) for each row added to the table.

To mythinking, simply using (Max(ROWID) + 1) in my SELECT statement will notwork as it will only be evaluated once so if I am adding 1000 recordsand Max(ROWID) is 1234, all 1000 entries will end up having a ROWID of1235.

Is there a way to accomplish this?

Thanks

Hi,

you can increment the row id locally in your program after doing an update in a loop:
rowID = <select max rowid blablabla>
while <there are still rows to add>
Command.executenonquery() - using rowID
rowID +=1
Loop

you can also use a trigger in the database to update the rowid column after insert

|||

Yes.

You can readMax(ROWID) from database intoApplication("id") at the start of web application,e.g. 1234 to avoid frequent access to database.

After insert just add id by 1.

Hope it helps.