SQL Server – Return value after INSERT

sqlsql serversql-server-2008

I'm trying to get a the key-value back after an INSERT-statement.
Example:
I've got a table with the attributes name and id. id is a generated value.

    INSERT INTO table (name) VALUES('bob');

Now I want to get the id back in the same step. How is this done?

We're using Microsoft SQL Server 2008.

Best Answer

No need for a separate SELECT...

INSERT INTO table (name)
OUTPUT Inserted.ID
VALUES('bob');

This works for non-IDENTITY columns (such as GUIDs) too