CODEWITHSUNDEEP
sql-server
a1ex07
a1ex07

Reputation: 37382

SQL Server: is it possible to get recently inserted identity column value without table variable

Let's say I have a table 

my_table(id int identity(1,1) not null primary key, data varchar(100))

I want to write a procedure that inserts a new row into that table and returns id. I tried

DECLARE @new_id INT;    
SELECT @new_id = id FROM
(
INSERT INTO my_table(data) OUTPUT inserted.id VALUES ('test') 
) as NewVal(id)

That code doesn't work (I got "A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed in a SELECT statement that is not the immediate source of rows for an INSERT statement."). However, if I use a table variable, I can do 

DECLARE @new_id INT;    
DECLARE @tmp_table TABLE(int id);
INSERT INTO @tmp_table
SELECT id FROM
(
INSERT INTO my_table(data) OUTPUT inserted.id VALUES ('test')  
) as NewVal(id);
// OR 
INSERT INTO my_table(data) OUTPUT inserted.id INTO @tmp_table VALUES ('test') ;
SELECT @new_id = id FROM @tmp_table;

Is it possible to achieve the same functionality without using table variable ?

UPDATE
Thanks for quick responses, +1 to everyone for solution with SCOPE_IDENTITY. That's probably my fault, I should have asked the question clearly - I do use MERGE (an example would be much longer, so I posted INSERT instead) , not INSERT so SCOPE_IDENTITY doesn't really work for me.

Upvotes: 1

Views: 7613

Answers (5)

Mikael Eriksson
Mikael Eriksson

Reputation: 138990

A bit shorter version than nesting in a insert statement is using output...into.

declare @tmp_table table(actiontaken nvarchar(10), id int);

merge my_table
using (values ('test')) as S(data)
on 0=1
when not matched then 
  insert (data) values (S.data)
output $action, inserted.id into @tmp_table;

I do believe that you should use a table variable from the merge. The output may contain more than one row.

Upvotes: 5

Duncan Howe
Duncan Howe

Reputation: 3025

If you use

select @@identity

you will get the last value entered into the identity column from anywhere.

If you want the value entered into the column from the script that just ran, you should use

select SCOPE_IDENTITY()

If you just want the last value inserted into the identity column for a given table from any statement in any session, you should use

select IDENT_CURRENT('tablename')

Upvotes: 3

JoDG
JoDG

Reputation: 1356

select @@identity or select SCOPE_IDENTITY() will return the value you're looking for

Upvotes: 1

Quantum Elf
Quantum Elf

Reputation: 752

SELECT SCOPE_IDENTITY() will get the last inserted identity value in the scope.

Upvotes: 4

AdaTheDev
AdaTheDev

Reputation: 147324

Yes, you can just return SCOPE_IDENTITY after the insert (this is safer than @@IDENTITY due to the scoping differences).

i.e.

INSERT my_table (data) VALUES ('test')
SELECT SCOPE_IDENTITY()

Upvotes: 5

Related Questions