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;

One thought on “Return data by using the OUTPUT clause

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s