CODEWITHSUNDEEP
sqlsql-servert-sqlidentityauto-increment
skiphoppy
skiphoppy

Reputation: 103031

How do I return a new IDENTITY column value from an SQLServer SELECT statement?

I'm inserting into an SQLServer table with an autoincrementing key field. (I believe this is called an IDENTITY column in SQLServer.)

In Oracle, I can use the RETURNING keyword to give my INSERT statement a results set like a SELECT query that will return the generated value:

INSERT INTO table
(foreign_key1, value)
VALUES
(9, 'text')
RETURNING key_field INTO :var;

How do I accomplish this in SQLServer?

Bonus: Okay, nice answers so far, but how do I put it into a single statement, if possible? :)

Upvotes: 12

Views: 36336

Answers (7)

gbn
gbn

Reputation: 432742

SELECT SCOPE_IDENTITY()

Edit: Having a play...

If only the OUTPUT clause supported local variables.

Anyway, to get a range of IDs rather than a singleton

DECLARE @Mytable TABLE (keycol int IDENTITY (1, 1), valuecol varchar(50))

INSERT @Mytable (valuecol) 
OUTPUT Inserted.keycol
SELECT 'harry'
UNION ALL
SELECT 'dick'
UNION ALL
SELECT 'tom'

Edit 2: In one call. I've never had occasion to use this construct.

DECLARE @Mytable TABLE (keycol int IDENTITY (1, 1), valuecol varchar(50))

INSERT @Mytable (valuecol) 
OUTPUT Inserted.keycol
VALUES('foobar')

Upvotes: 9

Rob Garrison
Rob Garrison

Reputation: 7244

It depends on your calling context.

If you're calling this from client code, you can use OUTPUT and then read the value returned.

DECLARE @t TABLE (ColID int IDENTITY, ColStr varchar(20))

INSERT INTO @t (ColStr)
OUTPUT Inserted.ColID
VALUES ('Hello World')

Result:

      ColID
-----------
          1

If you're wrapping this in a stored procedure, using OUTPUT is more work. There, you'll want to use SCOPE_IDENTITY(), but you can't do it in a single statement. Sure, you can put multiple statements on a single line with a ';' separator, but that's not a single statement.

DECLARE @idValue    int
DECLARE @t TABLE (ColID int IDENTITY, ColStr varchar(20))

INSERT INTO @t (ColStr) VALUES ('Hello World')

SELECT @idValue = SCOPE_IDENTITY()

Result: @idValue variable contains identity value. Use an OUTPUT parameter to return the value.

Upvotes: 2

Bhaskar
Bhaskar

Reputation: 10691

INSERT INTO table(foreign_key1, value)VALUES(9, 'text');SELECT @@IDENTITY;

Upvotes: 0

richardtallent
richardtallent

Reputation: 35404

In general, it can't be done in a single statement.

But the SELECT SCOPE_IDENTITY() can (and should) be placed directly after the INSERT statement, so it's all done in the same database call.

Example:

mydb.ExecuteSql("INSERT INTO table(foreign_key1, value) VALUES(9, 'text'); SELECT SCOPE_IDENTITY();");

You can use OUTPUT, but it has some limitations you should be aware of:

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

Upvotes: 18

Cade Roux
Cade Roux

Reputation: 89741

You can use OUTPUT INTO, which has the additional benefits of being able to capture multiple identities inserted.

Upvotes: 0

Tom H
Tom H

Reputation: 47402

In addition to @@IDENTITY, you should also look into SCOPE_IDENTITY() and IDENT_CURRENT(). You most likely want SCOPE_IDENTITY(). @@IDENTITY has a problem in that it might return an identity value created in a trigger on the actual table that you're trying to track.

Also, these are single-value functions. I don't know how the Oracle RETURNING keyword works.

Upvotes: 4

Ken Burkhardt
Ken Burkhardt

Reputation: 3558

SCOPE_IDENTITY

Upvotes: 2

Related Questions