locked
Retrieve the last id inserted RRS feed

  • Question

  • I have a problem when trying to insert the last ID with the following query:

    echo  $result = odbc_exec($connection, "SELECT @"."@IDENTITY AS Ident"); 

    Can you explain what am I doing wrong?
    • Moved by Kalman Toth Thursday, April 25, 2013 3:35 PM Not db design
    Wednesday, April 24, 2013 10:39 AM

Answers

  • To return the last IDENTITY value assigned on the connection, specify @@IDENTITY or SCOPE_IDENTITY().  SCOPE_IDENTITY() is generally preferred as it is not affected by triggers.  I don't know php but I believe the following should do the trick:

    echo  $result = odbc_exec($connection, "SELECT @@IDENTITY AS Ident;"); 
    echo  $result = odbc_exec($connection, "SELECT SCOPE_IDENTITY() AS Ident;"); 


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Naomi N Thursday, April 25, 2013 3:49 PM
    • Marked as answer by Kalman Toth Thursday, May 2, 2013 8:11 AM
    Wednesday, April 24, 2013 10:46 AM

All replies

  • To return the last IDENTITY value assigned on the connection, specify @@IDENTITY or SCOPE_IDENTITY().  SCOPE_IDENTITY() is generally preferred as it is not affected by triggers.  I don't know php but I believe the following should do the trick:

    echo  $result = odbc_exec($connection, "SELECT @@IDENTITY AS Ident;"); 
    echo  $result = odbc_exec($connection, "SELECT SCOPE_IDENTITY() AS Ident;"); 


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Proposed as answer by Naomi N Thursday, April 25, 2013 3:49 PM
    • Marked as answer by Kalman Toth Thursday, May 2, 2013 8:11 AM
    Wednesday, April 24, 2013 10:46 AM
  • Hi,

    If you want to retrieve the last identity value inserted into the column from any connection, use this command:

    IDENT_CURRENT(‘database name’)
    Your solution isn’t working because @@Identity works on current connection only. It disregards other connections.
    Wednesday, April 24, 2013 10:51 AM
  • Best way to retrieve last IDENTITY inserted by the OUTPUT clause:

    http://www.sqlusa.com/bestpractices2005/outputidentitycapture/


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Thursday, April 25, 2013 3:35 PM