Return data by using the OUTPUT clause

This objective may include but is not limited to: INSERTED, DELETED, INTO.

Sometimes you might need values from an insert/update/delete. One method is to use DML triggers, which I’ll cover later. The other way is (available from SQL Server 2005) is to use the OUTPUT clause in conjunction with a DML statement (DML is stands for Data Modification Language, one of the fundamental parts of SQL).

The syntax is simple and straightforward:

INSERT INTO Employees
(LastName, FirstName)
OUTPUT inserted.EmployeeID INTO AuditTable
VALUES(‘Gergely’, ‘Koncz’);

A delete is essentially the same, but you need to refer to the table affected as deleted:

DELETE FROM Employees
OUTPUT deleted.EmployeeID INTO AuditTable
WHERE EmployeeID = 10;

The interesting part is an update. An update, from the point of view of a trigger, or the OUTPUT clause, is a DELETE and an INSERT statement. You can get the values of the specified row before the update operation by querying the deleted virtual table, and the values after the update by the inserted virtual table. The syntax:

UPDATE Employees
SET LastName = ‘Zac’
OUTPUT deleted.EmployeeID, inserted.EmployeeID INTO AuditTable
WHERE EmployeeID = 10;