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.

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 to 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.

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)

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')

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

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