Menu
I'm trying to get a the key-value back after an INSERT-statement. Example: I've got a table with the attributes name and id. id is a generated value.
INSERT INTO table (name) VALUES('bob'); Now I want to get the id back in the same step. How is this done?
We're using Microsoft SQL Server 2008.
207
SQL Server provides four ways to retrieve the newly generated identity value after rows have been inserted into a table: @@Identity. Scope_Identity() Ident_Current()
An SQL INSERT statement writes new rows of data into a table. If the INSERT activity is successful, it returns the number of rows inserted into the table. If the row already exists, it returns an error. Multiple rows can be inserted into a table.
To obtain the value immediately after an INSERT , use a SELECT query with the LAST_INSERT_ID() function. For example, using Connector/ODBC you would execute two separate statements, the INSERT statement and the SELECT query to obtain the auto-increment value.
We use SCOPE_IDENTITY() function to return the last IDENTITY value in a table under the current scope. A scope can be a module, trigger, function or a stored procedure. We can consider SQL SCOPE_IDENTITY() function similar to the @@IDENTITY function, but it is limited to a specific scope.
No need for a separate SELECT...
INSERT INTO table (name) OUTPUT Inserted.ID VALUES('bob'); This works for non-IDENTITY columns (such as GUIDs) too
119
Use SCOPE_IDENTITY() to get the new ID value
INSERT INTO table (name) VALUES('bob'); SELECT SCOPE_IDENTITY() http://msdn.microsoft.com/en-us/library/ms190315.aspx
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
