The Output clause in SQL Server will allow you to see your most recent data modifications. Typically, when you run Data Manipulation Language (DML) statements such as an INSERT, UPDATE, or DELETE, you receive a message that one or more rows have been affected. (Documentation)

Using the Output Clause

But what if you want to see what records have just been modified? You could run a SELECT statement, but that wouldn’t return only the records that were just modified. That is why we have the OUTPUT clause. The output clause can use the INSERTED and/or the DELETED virtual tables. These virtual tables show us what records were just modified. The DML statement being used determines whether the INSERTED, DELETED, or both virtual tables will be used with the OUTPUT clause.

Demonstration of the Output Clause

To setup our demonstration, we will create a simple test table with two columns.

CREATE TABLE dbo.TestTable
(TestID tinyint IDENTITY,
TestName char(30) NOT NULL)

Using the INSERTED virtual table

Next, we will use the INSERT statement with the Output clause. By running this statement instead of getting a message stating, “4 rows affected,” the output clause will use the INSERTED virtual table to display just the records that were modified.

INSERT INTO dbo.TestTable
	OUTPUT inserted.*
VALUES ('First Row'), ('Second Row'),
	   ('Third Row'), ('Fourth Row')

Using the DELETED virtual table

Next, we will see how the DELETE statement and the OUTPUT clause work together. Again, the OUTPUT clause will use the DELETED virtual table to return just the record that was modified.

DELETE dbo.TestTable
	OUTPUT deleted.*
WHERE TestID = 4

Updates use both the INSERTED and DELETED virtual tables

And finally, we will show an UPDATE statement where the OUTPUT clause will use both the INSERTED and DELETED tables to see what the new value is being added into the table and what old value was being removed from the modified table.

UPDATE dbo.TestTable
SET TestName = 'Updated Row'
	OUTPUT inserted.*, deleted.*
WHERE TestID = 3