Retrieving Autonumber on INSERTgreenspun.com : LUSENET : SQL Server Database Administration : One Thread
I'm using Access/SQL/ASP to make a web-app. I have a table which I use to store completed transactions, and I'm using the "AutoNumber" variable type to give each transaction a unique number.
The trouble is I need to relate information in other tables to the transaction which I just posted to the storage database. Basically I need a way to get at the AutoNumber which was automatically generated when I inserted the new recordrow.
I could probably get at it by SELECTing the AutoNumber WHERE all the data matches the data I just posted, but that seems like an inefficient and inelegant way to do something pretty simple.
-- Anonymous, July 07, 1999
If you can guarantee that no other process is adding data to completed transactions table (for instance, by using a lock) you can select the maximum autonumber instead of specifying all the data.
With SQL Server you could also use a trigger which references the inserted table.
Hope this helps,
-- Anonymous, July 13, 1999
The SQL statement to retrieve the last autonumber inserted is: "SELECT autonumber_column_name FROM table_name WHERE IdentityCol = @@Identity" You would need to ensure that there are no intervening inserts, as the last number used is returned to you. Maureen.
-- Anonymous, July 26, 1999