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

Question

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? :)

Solution 1

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

Solution 2

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')

Solution 3

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.

Solution 4

SCOPE_IDENTITY

Solution 5

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.

View more solutions

Comments

[James Conigliaro]

I second this. SCOPE_IDENTITY() returns the most recent identity within the scope of your most recent query. Other methods @@IDENTITY and IDENT_CURRENT() arent limited to a specific scope

[Yaser Har]

Incredible. I had no idea I could combine statements like that. Clever, nonstandard, hideous, useful ... all these adjectives come to mind. :)

[ZygD]

It canbe done in one statement in SQL Server 2005 with the OUTPUT