Quick Reminder: Transact-SQL OUTPUT Clause

The OUTPUT clause is a useful tool for getting values just inserted, updated, or deleted from a table. You'll find several uses for the OUTPUT clause on your own but here's a few examples to get you started.

Getting the Identify Value of a Just-inserted Row

You can use the OUTPUT clause to get the value of an identify column for a newly inserted row. Traditionally, you'd have to jump through a hoop or two to get the value and in some cases that value couldn't be relied upon to be accurate. (For instance, if several updates were occurring at the same time, you might get the identify column value of another insertion -- not the one you performed.)

-- example #1
CREATE TABLE #BrgTest (
[TestID] int identity primary key not null,
[Name] varchar(50),
[Description] varchar(255)
);

INSERT INTO #BrgTest ([Name], [Description])
OUTPUT INSERTED.[TestID], INSERTED.[Name], INSERTED.[Description]
VALUES ('Pancake', 'A goofy dog')

DROP TABLE #BrgTest

The results look like this:

ID	Name	Description
1	Pancake	A goofy dog

Getting the Value of Several Rows Just Inserted

What if you need to get the values of several rows just inserted? Create a table variable (or temp table) and use the OUTPUT clause to insert the values into the table variable (or temp table). Later, you can revisit the values in the table and perform any work you need done using the values there.

The example below shows t how to store the OUTPUT values into a table variable.

-- example #2
CREATE TABLE #BrgTest (
[TestID] int identity primary key not null,
[Name] varchar(50),
[Description] varchar(255)
);


DECLARE @Holding TABLE ([ID] int)

INSERT INTO #BrgTest ([Name], [Description])
OUTPUT INSERTED.[TestID] INTO @Holding([ID])
VALUES ('Pancake', 'A goofy dog')

INSERT INTO #BrgTest ([Name], [Description])
OUTPUT INSERTED.[TestID] INTO @Holding([ID])
VALUES ('Smackers', 'A sassy cat')

SELECT
*
FROM
@Holding

DROP TABLE #BrgTest


The results look like this:

ID

1

2

Getting Values from a Just-Deleted Row

There may be scenarios where you wish to revisit a value from a row you deleted. The example below shows how to hang onto the identity column value of a row you deleted. You can then use that value later on.

Once again, we use a table to capture the output of the OUTPUT clause:

-- example #3

CREATE TABLE #BrgTest (
[TestID] int identity primary key not null,
[Name] varchar(50),
[Description] varchar(255)
);

DECLARE @JustDeleted TABLE([ID] int);

INSERT INTO #BrgTest ([Name], [Description])
OUTPUT INSERTED.[TestID] INTO @Holding([ID])
VALUES ('Pancake', 'A goofy dog')

INSERT INTO #BrgTest ([Name], [Description])
OUTPUT INSERTED.[TestID] INTO @Holding([ID])
VALUES ('Smackers', 'A sassy cat')

DELETE FROM

#BrgTest

OUTPUT

DELETED.[TestID] INTO @JustDeleted([ID])

WHERE

[Name] = 'Pancake';

SELECT * FROM @JustDeleted;

DROP TABLE #BrgTest;

Why do we use table variables and temp tables to capture the results of the OUTPUT clause? We do this because tables work and scalar variables do not.

What's with the INSERTED and DELETED Tables?

Look at the code snippet below:

OUTPUT INSERTED.[TestID] INTO @Holding([ID])

Note the table INSERTED. This table, along with the DELETED table, are special in-memory, temporary tables used by SQL Server to record the old and new values of rows inserted into and deleted from "real" tables. When an UPDATE occurs, the previous state of the row is recorded in the DELETED table and the new state of the row is recorded in the INSERTED table. Pretty neat, right?

Note that you can't directly query the INSERTED and DELETED tables, nor can you manipulate them. In other words, you can't perform SELECT * FROM DELETED, nor can you insert a row into the INSERTED table.

Be aware, too, that the tables only hold the most recent activity. If you perform two insert statements in a row, INSERTED only holds the value for the last-performed statement.