HOW do i use SELECT @@identity in sql server?

Question

i am currently calling SELECT @@identity from VBA in mysql:

Set rs = cn.Execute("SELECT @@identity", , adCmdText)

but since i am going to be working with sql server db instead of mysql, i would like to know how to make this statement sql-server friendly

would it just be Set rs = cn.Execute("SCOPE_IDENTITY()", , adCmdText) ??

Answer 1

If you're asking about the T-SQL side of it, then you should be able to just use the same statement (i.e. 'SELECT @@identity').

Answer 2

Both SQL statements are valid, with one exception. Change

"SCOPE_IDENTITY()"

to

"SELECT SCOPE_IDENTITY()"

The difference between the two is that the @@identity variable contains the most recent identity value on the SQL Server (global perspective). The SCOPE_IDENTITY() function returns the most recent local identity.

You can find more on the SCOPE_IDENTITY here.