OUTPUT Clause in SQL Server

In SQL Server 2005 and newer, you can add the OUTPUT clause on the DELETE, INSERT and UPDATE statements, and in SQL Server 2008 and newer also on MERGE statements.
The results can be inserted into a table or table variable.
It allows you to access to the inserted and deleted rows.

To demonstrate the OUTPUT clause we will use two tables: Names and NamesArchive
To make a table 'Names' that we will use to test the statements we will use the following query in AdventureWorks database:

SELECT FirstName, MiddleName, LastName INTO Names FROM Person.Person

We will create NamesArchive table:

CREATE TABLE [dbo].[NamesArchive](
[FirstName] nvarchar(100) NOT NULL,
[MiddleName] nvarchar(100) NULL,
[LastName] nvarchar(100) NOT NULL
)

DELETE:

DELETE FROM Names
OUTPUT deleted.FirstName, deleted.LastName
WHERE MiddleName = 'Ann'

output_delete

DELETE FROM Names
OUTPUT deleted.FirstName, deleted. MiddleName, deleted.LastName INTO NamesArchive
WHERE MiddleName = 'R'

output_delete_archive

INSERT:

INSERT INTO Names
OUTPUT inserted.* INTO NamesArchive
VALUES('Adam', 'A', 'Adams')

output_insert

UPDATE:

When updating, inserted values are the new updated values, and deleted are the old values before the UPDATE statement:

UPDATE Names
SET FirstName = 'David', MiddleName = 'D', LastName ='Davis'
OUTPUT inserted.*, deleted.*
WHERE FirstName = 'Adam' AND MiddleName = 'A' AND LastName ='Adams'

output_update

MERGE:

With MERGE, you can also use a column called $action, that shows the type of operation performed:

OUTPUT $action, inserted.*, deleted.*

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">