SQL Server – Return value after INSERT

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.

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