The OUTPUT clause

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. See old and new row values produced by a data change. Run changing examples only on a disposable training instance. The example uses a session-local object or rolls back its changes.

Run the example

BEGIN TRANSACTION;
UPDATE dbo.Products SET ListPrice = ListPrice + 1.00
OUTPUT inserted.ProductID, deleted.ListPrice AS OldPrice,
       inserted.ListPrice AS NewPrice
WHERE ProductID = 1;
ROLLBACK TRANSACTION;

Read it step by step

1. UPDATE changes one row temporarily.
2. deleted refers to the old row image and inserted to the new row image for OUTPUT.
3. ROLLBACK restores the shared product price after the result is returned.

Check the result

OUTPUT values describe the attempted update within the transaction; the database is unchanged after rollback.

Practice

Use OUTPUT to show old and new IsActive values when temporarily setting ProductID 2 to 0.

BEGIN TRANSACTION;
UPDATE dbo.Products SET IsActive = 0
OUTPUT inserted.ProductID, deleted.IsActive AS OldFlag,
       inserted.IsActive AS NewFlag
WHERE ProductID = 2;
ROLLBACK TRANSACTION;