• How to get last inserted Record ID in SQL Server

    by  • June 17, 2013 • Microsoft SQL Server • 0 Comments

    Auto increment identity column automatically insert record Id with the specified incremental value. Several times we need to get the last inserted record id. There are several ways to achieve this task. Let’s take a look how we can get last inserted record/row id value.

    @@IDENTITY

    Most common and important method used to get last inserted value is @@IDENTITY. It returns the last inserted value from any table in the current session. For example, if we are using any trigger insert record into another table as a result of insert statement of a table then @@IDENTITY will return the record id from latest inserted table. So it considers current session and it is limited to current session only.

    SCOPE_ IDENTITY ()

    Another method we can use to get last inserted value is SCOPE_ IDENTITY(). It considers your current session but it also considers your current scope. That means it will return last inserted value in current scope and it will not return last inserted value generated from any trigger of user defined functions. In other words you can say that it will only return the last inserted value that you explicitly insert rather than the value generated from any trigger or user defined function.

    IDENT_CURRENT(‘TableName’)

    As you can guess, IDENT_CURRENT(‘TableName’) will return last inserted value of specified table. It will not consider any session or scope. It only returns the last inserted value of that specified table regardless of any session or scope.

    There are other ways you can use to get last inserted value from SQL Server database table but generally these techniques are not considered safe.

    You can use following statements but be careful while using these statements to get last inserted value because sometimes it can cause problems.




    Namecheap.com
    Avatar of GetCodeSnippet.com

    About

    GetCodeSnippet.com provides you high quality Microsoft .NET development related articles, tutorials and sample code for ASP.NET, C#, VB.NET, AJAX, WCF, WPF, Web Services, Windows Services, SQL Server and other technologies.

    Leave a Reply