SQL Server - Return value after INSERT
No need for a separate SELECT...
INSERT INTO table (name)OUTPUT Inserted.IDVALUES('bob');
This works for non-IDENTITY columns (such as GUIDs) too
Use SCOPE_IDENTITY()
to get the new ID value
INSERT INTO table (name) VALUES('bob');SELECT SCOPE_IDENTITY()
INSERT INTO files (title) VALUES ('whatever'); SELECT * FROM files WHERE id = SCOPE_IDENTITY();
Is the safest bet since there is a known issue with OUTPUT Clause conflict on tables with triggers. Makes this quite unreliable as even if your table doesn't currently have any triggers - someone adding one down the line will break your application. Time Bomb sort of behaviour.
See msdn article for deeper explanation: