Skip to main content

How to get last inserted id in sql server


SELECT @@IDENTITY
It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.
@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current scope. If you have a trigger on a table that causes an identity to be created in another table, you will get the identity that was created last, even if it was the trigger that created it.
SELECT SCOPE_IDENTITY()
It returns the last IDENTITY value produced on a connection and by a statement in the same scope, regardless of the table that produced the value.
SCOPE_IDENTITY(), like @@IDENTITY, will return the last identity value created in the current session, but it will also limit it to your current scope as well. In other words, it will return the last identity value that you explicitly created, rather than any identity that was created by a trigger or a user defined function.
SELECT IDENT_CURRENT(‘tablename’)
It returns the last IDENTITY value produced in a table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the identity value generated for a specific table in any session and any scope.
To avoid the potential problems associated with adding a trigger later on, always use SCOPE_IDENTITY() to return the identity of the recently added row in your T SQL Statement or Stored Procedure.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

example: 

This was my insert procedure

INSERT INTO tblMaxConUser
           (--UserID,
           UserType
           ,UserName
           ,RoleID
           ,Password
           ,FirstName
           ,LastName
           ,PhoneNo
           ,EmailID
           ,Notes
           
           ,CreatedUserID
           ,MaxConID
           , SOID
           , CompanyId
           ,CreatedUserDate
           ,Status
           ,Signature
           ,UseSignature)
     VALUES
           (--@UserID,
           @UserType
           ,@UserName
           ,@RoleID
           ,@Password
           ,@FirstName
           ,@LastName
           ,@PhoneNo
           ,@EmailID
           ,@Notes
           
           ,@CreatedUserID
           ,@MaxConID
           ,@SOID
           ,@CompanyId
           ,GETDATE() 
           ,@Status
           ,@Signature
           ,@UseSignature)
           SELECT 'User Created_' + CAST(@@IDENTITY as varchar(5)) as umsg


"@@IDENTITY" gave me the last inserted id in this session for this stored procedure
Labels:

Popular posts from this blog

Fake CVR Generator Denmark

What Is Danish CVR The Central Business Register (CVR) is the central register of the state with information on all Danish companies. Since 1999, the Central Business Register has been the authoritative register for current and historical basic data on all registered companies in Denmark. Data comes from the companies' own registrations on Virk Report. There is also information on associations and public authorities in the CVR. As of 2018, CVR also contains information on Greenlandic companies, associations and authorities. In CVR at Virk you can do single lookups, filtered searches, create extracts and subscriptions, and retrieve a wide range of company documents and transcripts. Generate Danish CVR For Test (Fake) Click the button below to generate the valid CVR number for Denmark. You can click multiple times to generate several numbers. These numbers can be used to Test your sofware application that uses CVR, or Testing CVR APIs that Danish Govt provide. Generate
Post a Comment
Read more

How To Iterate Dictionary Object

Dictionary is a object that can store values in Key-Value pair. its just like a list, the only difference is: List can be iterate using index(0-n) but not the Dictionary . Generally when we try to iterate the dictionary we get below error: " Collection was modified; enumeration operation may not execute. " So How to parse a dictionary and modify its values?? To iterate dictionary we must loop through it's keys or key - value pair. Using keys
Post a Comment
Read more

How To Append Data to HTML5 localStorage or sessionStorage?

The localStorage property allows you to access a local Storage object. localStorage is similar to sessionStorage. The only difference is that, while data stored in localStorage has no expiration time untill unless user deletes his cache, data stored in sessionStorage gets cleared when the originating window or tab get closed. These are new HTML5 objects and provide these methods to deal with it: The following snippet accesses the current domain's local Storage object and adds a data item to it using Storage.setItem() . localStorage.setItem('myFav', 'Taylor Swift'); or you can use the keyname directly as : localStorage.myFav = 'Taylor Swift'; To grab the value set in localStorage or sessionStorage, we can use localStorage.getItem("myFav"); or localStorage.myFav There's no append function for localStorage or sessionStorage objects. It's not hard to write one though.The simplest solution goes here: But we can kee
Post a Comment
Read more