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

SQL Server - Return value after INSERT