sql - 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