Scope_identity and @ identity

SCOPE_IDENTITY and @ IDENTITY are used to obtain the last Identifier value returned from any table in the current session, simply put, after executing an insert statement, use the global variable @ IDENTITY to obtain the ID number of the insert record. However, the problem is that @ IDENTITY is global, therefore, its functions are reflected in all scopes, one operation, one trigger, and one stored procedure is called one scope. In this case, if multiple scopes occur, @ IDENTITY the ID number obtained is the result of the last scope. In this case, we will use the SCOPE_IDENTITY method. SCOPE_IDENTITY only returns the value inserted into the current scope; @ IDENTITY is not limited to a specific scope.
Usage: select SCOPE_IDENTITY () as ID from [table] select @ IDENTITY as ID from [table]
Instance:
SQL = "set nocount on; insert into [Table] (Item) values ('" & Item & "')" SQL = SQL &"; select @ IDENTITY as ID from [Table]; set nocount off ;"