What is the OUTPUT clause and when do you use it?
Quick Answer
OUTPUT returns the rows affected by an INSERT/UPDATE/DELETE/MERGE, exposing the inserted and deleted pseudo-tables. It's how you capture generated identity/GUID values for a bulk insert, write an audit trail in the same statement, or atomically claim rows from a queue.
The OUTPUT clause lets a data-modification statement return the rows it changed, through the inserted and deleted pseudo-tables — the same ones triggers use. I reach for it in three situations: capturing all the generated ids from a multi-row insert, since SCOPE_IDENTITY only gives you one; writing an audit record of old-versus-new values in the same statement so it can't get out of sync; and implementing a queue, where DELETE ... OUTPUT deleted.* with READPAST lets multiple workers claim different rows atomically.
INSERT ... OUTPUT inserted.Id, inserted.CreatedUtc INTO @NewRows ...— get all new keys at once (SCOPE_IDENTITY()only gives the last single-row insert).- Queue pattern:
DELETE TOP (10) FROM Queue WITH (READPAST) OUTPUT deleted.* INTO @batch— dequeue and read in one atomic step. UPDATE ... OUTPUT deleted.Status AS OldStatus, inserted.Status AS NewStatus INTO Audit.OUTPUT ... INTOa table has restrictions when the target has triggers or certain FKs.