Sql @@identity returns null

This returns the last generated identity for the current session and scope. As per my experience, if you want to get the last inserted record's ID.. I never using AutoNumber fields in any database I design because they cause the most problems when I write Synchronize programs. Andy Warren. This is really all the ident does, it just manages concurrency for you. Connect with me on LinkedIn Follow me on Twitter. Yes, although any table I have with a numeric Key is only a lookup table and gets modified once every You must be logged in to reply to this topic.

Login to reply. November 7, at pm Add a comment. Active Oldest Votes. Improve this answer. While true, I don't think it was necessary to get into this much detail about an obscure bug that only manifests itself when you're inserting several rows and trying to retrieve a single identity. Just to clarify, as I had some difficulty understanding this myself, x is the target table which data is going into and y is the table created for the purpose of collecting the IDs of each new row inserted into x in the final INSERT statement.

Thanks, that confirms how I thought it was behaving. If your insert on a table without identity however causes a trigger to fire which might be inserting e.

Does that make sense to you?? So please undo that downvote - it's totally not called for I am facing a similar issue. As far as I know the insert is the only statement in scope.

I will check to see if I have a trigger on this table. Connect and share knowledge within a single location that is structured and easy to search. I have stored procedure that take dbName and insert record in that DB. I want to get the id of the last inserted record. Does it return the id in the scope of the table, regardless of trigger? Like Oded says, the problem is that you're asking for the identity before you execute the insert. Stack Overflow for Teams — Collaborate and share knowledge with a private group.

Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Ask Question. Asked 10 years, 4 months ago. Active 4 years, 4 months ago. Viewed 8k times. Feedback will be sent to Microsoft: By pressing the submit button, your feedback will be used to improve Microsoft products and services.

Privacy policy. If the statement fires one or more triggers that perform inserts that generate identity values, calling IDENTITY immediately after the statement returns the last identity value generated by the triggers.

If a trigger is fired after an insert action on a table that has an identity column, and the trigger inserts into another table that does not have an identity column, IDENTITY returns the identity value of the first insert.