Convert MySQL LAST_INSERT_ID() to MSSQL 2005

Databases
1

Hi,

I’m converting an ASP site that is using MySQL to run on MSSQL 2005.

On some pages, after a new record has been created, I need the ID (primary key) of the newly created record to pass as a querystring to the next page (for various reasons). Currently I use the MySQL LAST_INSERT_ID() to grab the last entered “ID” immediately after the INSERT code. The Last_Insert_ID() code looks like this:

Set rs1 = Server.CreateObject("ADODB.Recordset")
	rs1.Open "SELECT LAST_INSERT_ID()", dbconn
		tmpmemId = rs1(0)
	rs1.Close

and assigns the last auto-increment ID to the variable “tmpmemId”.

Is there an equivilant function in MSSQL 2005 that will achieve the same result?

Hope someone can help.

2

Yes, this is quite straightforward. In fact, there are several functions that will do you want, but they have subtle differences in their behaviour. They are @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT(). Check the Help for each of them, or take a look at this article, which gives a good overview:
Find the IDENTITY value of the last inserted row

Mike

3

Hi,

Thanks for pointing me in the right direction. This is my amended code that seems to work ok:

Set rs1 = Server.CreateObject("ADODB.Recordset")
	rs1.Open "SELECT IDENT_CURRENT('my_table') AS tmpmemId", MM_connDB_STRING
		tmpmemId = rs1("tmpmemId")
	rs1.Close

Many thanks.

4

But have you tested it in a situation where multiple users are inserting rows into the same table at the same time? The point about IDENT_CURRENT() (according to the article I refered to) is that it simply returns the highest current value of the ID, regardless of session and scope. You risk getting the wrong value if a second user inserts a row after the first user inserts a row but before the first user calls the function. (But perhaps that’s so unlikely that it’s not worth worrying about?)

Mike

5

But perhaps that’s so unlikely that it’s not worth worrying about?

In my experience the words “it’s so unlikely” are usually followed by “oh darn, it just happened” :slight_smile:
Even in a single-user environment something as simple as refreshing a webpage an cause unexpected concurrency that makes this very likely.

But perhaps the best argument is that the question was to find the MS-SQL equivalent of replace last_insert_id, which is @@identity. Identity_current is the equivalent of SELECT MAX(id)

6

True (which is why I put a question mark after it).

But even @@IDENTITY has a problem, in that it is scoped to the connection, but not to the scope (if you see what I mean). So, multiple INSERTs within the same connection would be OK, but not if one of them comes from a trigger, which is in a different scope (even though it’s the same table).

It seems to me that SCOPE_IDENTITY() is the best one to use, given that it doesn’t have any of the above problems.

Mike

7

It seems to me that SCOPE_IDENTITY() is the best one to use, given that it doesn’t have any of the above problems.

Indeed it does.