SQL Server - Return value after INSERT

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

4 years ago

Use SCOPE_IDENTITY() to get the new ID value

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



SELECT SCOPE_IDENTITY()

4 years ago

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.

4 years ago