How to get the following ID used in SQL 2005 and ASP?

Question

I previously got the following available autonumber used in Access by doing a simple query:

SELECT RecordNumber, Info FROM myTABLE WHERE 0=1 

That way, I could create a variable to hold the current Record, and it would use the same offline number that Access would use when I updated the row

Example

 rs.AddNew currentRecord = rs("RecordNumber") rsInfo = "SomeFormData" & currentRecord rs.Update rs.Close 

Now it is used to work with MS Access, but in SQL 2005 I do not return the Identity created by the new record. "SomeFormData" is inserted correctly, the RecordNumber field in SQL is filled with a new automatic number, but I do not have a RecordNumber in my variables, and I need it to continue to fill out related forms that store data in related tables and need to save the current registration numbers.

Question: is there a way to return this unique number when creating a new insert?

Answer 1

IDENT_CURRENT('tableName') (including single quotes) returns the current identifier value for this table. This value should be the last assigned identification value used in the table. In other words, you will have a row with this identity value already in the table, unless that row has been deleted. The identifier value that will be assigned to the next INSERT will be IDENT_CURRENT('tableName') + IDENT_INCR('tableName') .

I do not recommend relying on this. If you predefine the next identity value in this way, you are sure to be in a situation where another process makes an insert that actually gets this identifier before you use it, so your process ends up using the wrong identifier value.

It’s better to do your insertion first (even if you don’t have all the data yet), and use SCOPE_IDENTITY() to get the actual identifier.

You may wonder why SCOPE_IDENTITY() better than IDENT_CURRENT('tableName') . As the name implies, the former will give you the last identity value assigned in your current area (your batch, your saved proc, whatever), while the latter will give you the most recent identity assigned to the table by anyone. Even if you can call IDENT_CURRENT right after ' INSERT , it is still possible that there will be someone else INSERT between them, and IDENT_CURRENT will give you the identifier value that was the result of inserting them instead of yours, while SCOPE_IDENTITY will always give you yours.

EDIT

It is also worth mentioning that SCOPE_IDENTITY() stands for the similarly functioning @@IDENTITY . Although both return the last authentication value assigned in the current batch, @@IDENTITY affected by the inserts that occur inside triggers; SCOPE_IDENTITY() no.

Answer 2

SELECT CAST (Scope_Identity () AS INT)