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

Answer 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

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