Sql Server return the value of identity column after insert statement

Question

Is it possible in sql server using stored procedure to return the identity column value in a table against which some values are inserted? For example using stored procedure if we insert data in a table:

Table TBL

UserID integer, identity, auto-incremented
Name varchar
UserName varchar
Password varchar

So if I run the store procedure inserting some values like:

 Insert into TBL (Name, UserName, Password)
 Values ('example', 'example', '$2a$12$00WFrz3dOfLaIgpTYRJ9CeuB6VicjLGhLset8WtFrzvtpRekcP1lq')

How can I return the value of UserID at which this insertion will take place. I need The value of UserID for some other operations, can anybody solve this?

possible duplicate of [Best way to get identity of inserted row?](http://stackoverflow.com/questions/42648/best-way-to-get-identity-of-inserted-row) — bummi, Nov 13 '14 at 21:42
Possible duplicate of [SQL Server - Return value after INSERT](http://stackoverflow.com/questions/7917695/sql-server-return-value-after-insert) — Kaushal Khamar, Mar 25 '16 at 18:42

score 89 · Accepted Answer · answered Aug 01 '13 at 12:10

89

Insert into TBL (Name, UserName, Password) Output Inserted.IdentityColumnName
 Values ('example', 'example', 'example')

answered Aug 01 '13 at 12:10

Amit Singh

8,039
20
29

@AmitSingh What is the difference between this and scope_identity? The sentence `Inserted.IdentityColumnName` always will return a correct identity?, What will happen if insert at the same time from two inserts?. – Gaston Flores Aug 01 '13 at 13:16
@GastonF. it will always give the right value it give output of any colunmn not only identity means it can return whole row which is inserted..u can try by typnig inserted.anycolumnname – Amit Singh Aug 01 '13 at 14:07
2

@AmitSingh: great! +1, your post i helped me for find the differences in the use between this and `scope_identity()`, and I found something very crazy, the `scope_identity()` have a bug at least until version 2008, i do not know after that, anyway from Microsoft suggested use `Output Inserted...`...wow!. Check this [link](https://connect.microsoft.com/SQLServer/feedback/details/328811/scope-identity-sometimes-returns-incorrect-value). Thanks. – Gaston Flores Aug 01 '13 at 15:30
1

Note that the OUTPUT statement will return more than 1 row if your INSERT inserted more than 1 row, with a row containing each identity inserted, whereas SCOPE_IDENTITY() will return the last identity value inserted. So if you use SCOPE_IDENTITY() you better be sure that you can only ever insert 1 row. This is the case in your example. – Steve Ford Aug 02 '13 at 14:09
Is it possible to store the ID in a variable when using `OUTPUT`? – Stewart Aug 15 '23 at 15:52

score 14 · Answer 2 · edited Apr 01 '20 at 19:54

14

send an output parameter like

@newId int output

at the end use

    select @newId = Scope_Identity() 

     return @newId

edited Apr 01 '20 at 19:54

Diego Venâncio

5,698
2
49
68

answered Aug 01 '13 at 12:10

Md. Parvez Alam

4,326
5
48
108

score 8 · Answer 3 · answered Aug 01 '13 at 12:12

8

SELECT SCOPE_IDENTITY()

after the insert statement

Please refer the following links

http://msdn.microsoft.com/en-us/library/ms190315.aspx

answered Aug 01 '13 at 12:12

Ajay

6,418
18
79
130

score 5 · Answer 4 · answered Nov 13 '14 at 20:51

You can explicitly get back Identity columns using SqlServer's OUTPUT clause: Assuming you have an identity/auto-increment column called ID:

$sql='INSERT INTO [xyz].[myTable] (Field1, Field2, Field3) OUTPUT Inserted.ID VALUES  (1, 2, '3')';

Note that in Perl DBI you would then execute the INSERT statement, just like it was a SELECT. And capture the output like it was a SELECT

$sth->execute($sql);
@row=$sth->fetchrow_array; #Only 1 val in 1 row returned
print "Inserted ID: ", $row[0], "\n";

Presumably this is preferable because it doesn't require another request.

How is this any different than the accepted answer? – Solomon Rutzky Nov 13 '14 at 21:06 — Solomon Rutzky, Nov 13 '14 at 21:06

score 4 · Answer 5 · edited May 23 '17 at 12:34

4

Here goes a bunch of different ways to get the ID, including Scope_Identity:

https://stackoverflow.com/a/42655/1504882

edited May 23 '17 at 12:34

Community

1
1

answered Aug 01 '13 at 12:10

Elias

2,602
5
28
57

score 1 · Answer 6 · edited May 23 '17 at 11:33

1

You can use Scope_Identity() to get the last value.

Have a read of these too:

edited May 23 '17 at 11:33

Community

1
1

answered Aug 01 '13 at 12:08

Christian Phillips

18,399
8
53
82

@user2599269, I added a couple. – Christian Phillips Aug 01 '13 at 12:20

score -1 · Answer 7 · answered Aug 01 '13 at 12:10

-1

You can use SELECT @@IDENTITY as well

answered Aug 01 '13 at 12:10

Purplegoldfish

5,268
9
39
59

9

Please do not use that, as @@IDENTITY returns the last identity value use for ALL scopes, not just the current one, which can be wrong. – Oliver Aug 01 '13 at 12:30

Sql Server return the value of identity column after insert statement

7 Answers7

Linked