SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Convert MySQL LAST_INSERT_ID() to MSSQL 2005

    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:


    Code:
    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. #2
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,538
    Mentioned
    63 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SitePoint Enthusiast
    Join Date
    Jul 2009
    Posts
    52
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

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

    Code:
    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. #4
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,538
    Mentioned
    63 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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" :-)
    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. #6
    SitePoint Mentor silver trophybronze trophy
    Mikl's Avatar
    Join Date
    Dec 2011
    Location
    Edinburgh, Scotland
    Posts
    1,538
    Mentioned
    63 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by vinny42 View Post
    In my experience the words "it's so unlikely" are usually followed by "oh darn, it just happened" :-)
    True (which is why I put a question mark after it).

    Quote Originally Posted by vinny42 View Post
    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)
    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. #7
    SitePoint Enthusiast
    Join Date
    Nov 2013
    Posts
    26
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •