SQL Book

What is the SQL OUTPUT clause? Learn how to use it with clear examples.

SQL Server OUTPUT Clause

The purpose of the OUTPUT clause in SQL Server is to return a resultset of rows that were affected by one of the following statements: INSERT, UPDATE, DELETE, MERGE.

You can specify which columns from the affected rows are returned in the resultset. You can either return the resultset directly to the calling application or insert the resultset into a table/table variable/temp table by using the OUTPUT INTO version of the clause.

Let's see a quick example to demonstrate the OUTPUT clause. We will then discuss some of the additional aspects with further clear code examples. 

Using OUTPUT with an INSERT statement


-- Create a table with 2 columns
CREATE TABLE Organisation (
[Name] varchar(30),
[Country] varchar(30)
);
GO

-- Insert 3 rows of data into this table and use the OUTPUT
-- clause to return all of the columns for each row we insert
INSERT INTO Organisation
OUTPUT [Inserted].*
VALUES ('Super Co.', 'USA'),
('Great Co.', 'UK'),
('Fab Co.', 'India');


Returns:

Name Country
Super Co. USA
Great Co. UK
Fab Co. India

 

Wow! We have just returned a resultset without having to run a separate SELECT query after the INSERT statement.

The OUTPUT clause obtains its data to return from the Inserted and Deleted system tables. These tables exist in memory and are the same tables that can be accessed by SQL TRIGGERS.

They are populated with the rows of data that are modified by INSERT / UPDATE / DELETE statements.

The Inserted table is populated after the INSERT/UPDATE/MERGE statement completes but before any triggers (if defined on the table) fire.

The Deleted table is populated with the values from the row(s) before the UPDATE/DELETE/MERGE is applied.

UPDATE and MERGE statements populate both the Inserted and Deleted tables as illustrated in the table below.

Statements that trigger the population of the Inserted and Deleted tables

  INSERT UPDATE DELETE MERGE
INSERTED Y Y   Y
DELETED   Y Y Y

Specifying columns returned by the OUTPUT clause

We can continue our initial example by restricting which columns we return by specifying the actual column names rather than using *, just like we can in a SELECT statement.



IF OBJECT_ID('dbo.Organisation') IS NOT NULL
DROP TABLE dbo.Organisation;
GO

-- Create a table with 3 columns, one of which 
-- is an IDENTITY column which autogenerates its value
CREATE TABLE Organisation (
Id int IDENTITY(1,1),
[Name] varchar(30),
[Country] varchar(30)
);
GO

-- Insert 3 rows of data into this table and use the OUTPUT
-- clause to return only the Id and Name columns
INSERT INTO Organisation
OUTPUT [Inserted].[Id], [Inserted].[Name]
VALUES ('Super Co.', 'USA'),
('Great Co.', 'UK'),
('Fab Co.', 'India');

Returns:

Id Name
1 Super Co.
2 Great Co.
3 Fab Co.

We can see that resultset includes the auto-generated values from the Id column which was set as an IDENTITY column. That is because the Inserted table contains the values after the INSERT has occurred, not before the INSERT has occurred.

Let's see 2 more examples of using the OUTPUT clause but this time with UPDATE and DELETE statements

Output clause with an UPDATE statement

When adding the OUTPUT clause to an UPDATE statement we position it after the SET clause and before the WHERE clause.

As shown in the table above, an UPDATE statement actually causes a DELETE and an INSERT to occur and thus both the Deleted and Inserted tables are populated when we run an UPDATE.

What's great about the OUTPUT clause is that we can access both of these tables in the same column list and so access the 'old' version of the row and the 'new' version of the row. Let's see how this works in another quick example.


-- ---------------------------------------------------------
-- OUTPUT clause for UPDATE statement that returns data
-- from both the DELETED and INSERTED system tables
-- ---------------------------------------------------------
IF OBJECT_ID('dbo.Organisation') IS NOT NULL
DROP TABLE dbo.Organisation;
GO

-- Create a table with 3 columns, one of which 
-- is an IDENTITY column which autogenerates its value
CREATE TABLE Organisation (
Id int IDENTITY(1,1),
[Name] varchar(30),
[Country] varchar(30)
);
GO

-- insert a row
INSERT INTO Organisation ([Name], [Country])
VALUES ('Super Co.', 'USA');

-- Update a row and use the OUTPUT
-- clause to return the newly updated row data
UPDATE Organisation
SET [Name] = 'Supertastic Co.',
	[Country] = 'America'
OUTPUT [Deleted].[Id] As OldId, [Deleted].[Name] As OldName, 
		[Inserted].[Id] As [NewId], [Inserted].[Name] As [NewName]
WHERE Country = 'USA';

Returns:

OldId OldName NewId NewName
1 Super Co. 1 Supertastic Co.

Output clause with a DELETE statement

This example shows how easy it is to use OUTPUT with the DELETE statement. It also demonstrates how other SQL functions such as CONCAT or DATEDIFF can process the data from the system tables before being output. 


-- ---------------------------------------------------------
-- OUTPUT clause for DELETE statement that uses calls CONCAT
-- and DATEDIFF functions on the Deleted rows column values
-- ---------------------------------------------------------
IF OBJECT_ID('dbo.Employee') IS NOT NULL
DROP TABLE dbo.Employee;
GO

-- Create a table with 3 columns
CREATE TABLE Employee (
	FirstName varchar(30),
	LastName varchar(30),
	StartDate smalldatetime
);
GO

-- insert a row
INSERT INTO Employee
VALUES ('Homer', 'Simpson', '01/01/2020');

-- Delete a row and use the OUTPUT
-- clause to return the newly updated row data
DELETE Employee
OUTPUT CONCAT([Deleted].[FirstName], ' ', [Deleted].[LastName]) As [ExEmployeeName],
	DATEDIFF(d, Deleted.StartDate, GetDate()) As [DaysBeforeFired]
WHERE FirstName = 'Homer';

 Returns:

ExEmployeeName DaysBeforeFired
Homer Simpson. 8

So far we have just looked at returning the data to the calling application. We can, however, store the data in various types of table as the following examples show.

Inserting the output data into a table 


-- ---------------------------------------------------------
-- Store OUTPUT in a table
-- ---------------------------------------------------------

IF OBJECT_ID('dbo.Employee') IS NOT NULL
DROP TABLE dbo.Employee;
GO
IF OBJECT_ID('dbo.EmployeeHistory') IS NOT NULL
DROP TABLE dbo.EmployeeHistory;
GO

-- Create Employee table
CREATE TABLE Employee (
	Id int IDENTITY(1,1),
	FirstName varchar(30),
	LastName varchar(30),
	Salary int
);
GO

-- Create table for auditing changes to employees
CREATE TABLE EmployeeHistory (
	Id int,
	FirstName varchar(30),
	LastName varchar(30),
	Salary int,
	DateModified datetime	
)

-- insert an employee and record initial values in the EmployeeHistory table
INSERT INTO Employee
OUTPUT inserted.Id, Inserted.FirstName, Inserted.LastName, Inserted.Salary, GetDate()
INTO EmployeeHistory
VALUES ('Homer', 'Simpson', 20000);

-- UPDATE the employee and record new values in EmployeeHistory table
UPDATE Employee
SET Salary = 25000,
	LastName = 'Simpson-Rules'
OUTPUT Inserted.Id, Inserted.FirstName, Inserted.LastName, Inserted.Salary, GetDate()
INTO EmployeeHistory
WHERE Id = 1;

-- return all rows from EmployeeHistory table
SELECT * FROM EmployeeHistory;

Returns:

Id FirstName LastName Salary DateModified
1 Homer Simpson 20000 2020-01-09 21:32:37.460
1 Homer Simpson-Rules 25000 2020-01-09 21:32:37.470

Inserting the output data into a table variable

Here we will use the same example but instead of storing the data in a table, we will use an in-memory table variable.


-- ---------------------------------------------------------
-- Store OUTPUT in an in memory table variable
-- ---------------------------------------------------------

IF OBJECT_ID('dbo.Employee') IS NOT NULL
DROP TABLE dbo.Employee;
GO

-- Create Employee table
CREATE TABLE Employee (
	Id int IDENTITY(1,1),
	FirstName varchar(30),
	LastName varchar(30),
	Salary int
);
GO

-- Create table variable for auditing changes to employees
DECLARE @EmployeeHistory TABLE (
	Id int,
	FirstName varchar(30),
	LastName varchar(30),
	Salary int,
	DateModified datetime	
)

-- insert an employee and record initial values in the EmployeeHistory variable
INSERT INTO Employee
OUTPUT inserted.Id, Inserted.FirstName, Inserted.LastName, Inserted.Salary, GetDate()
INTO @EmployeeHistory
VALUES ('Homer', 'Simpson', 20000);

-- UPDATE the employee and record new values in EmployeeHistory variable
UPDATE Employee
SET Salary = 25000,
	LastName = 'Simpson-Rules'
OUTPUT Inserted.Id, Inserted.FirstName, Inserted.LastName, Inserted.Salary, GetDate()
INTO @EmployeeHistory
WHERE Id = 1;

-- return all rows from EmployeeHistory table variable
SELECT * FROM @EmployeeHistory;

Returns:

Id FirstName LastName Salary DateModified
1 Homer Simpson 20000 2020-01-09 21:33:37.460
1 Homer Simpson-Rules 25000 2020-01-09 21:33:37.470

Using both OUTPUT and OUTPUT INTO in the same statement

The next example shows how we can use more than one OUTPUT clause to both a) return data to the calling application as well as b) save data to a table, all in the same statement. Pretty useful?

Note that the 2 clauses can contain independent column lists to return different sets of data if you wish.


-- ------------------------------------------
-- Save data and return data in one statement
-- -------------------------------------------
IF OBJECT_ID('dbo.Album') IS NOT NULL
DROP TABLE dbo.Album;
GO
IF OBJECT_ID('dbo.ArchivedAlbum') IS NOT NULL
DROP TABLE dbo.ArchivedAlbum;
GO
CREATE TABLE Album (
	Title varchar(100),
	Artist varchar(100)
);
CREATE TABLE ArchivedAlbum(
	Title varchar(100),
	Artist varchar(100),
	DateArchived datetime
);
INSERT INTO Album
VALUES ('Greatest Hits', 'Queen'),
	('L.A. Woman', 'The Doors'),
	('Second Coming', 'The Stone Roses');

DELETE Album
OUTPUT Deleted.Title, Deleted.Artist, GetDate()
INTO ArchivedAlbum
-- add another clause that will return the data to the calling application
OUTPUT Deleted.Artist + ' - ' + Deleted.Title As [Archived]
WHERE Artist = 'The Doors';

-- return another resultset with rows from the ArchivedAlbums table
SELECT * FROM ArchivedAlbum;

Returns:

Archived
The Doors - L.A. Woman
Title Artist DateArchived
L.A. Woman The Doors 2020-01-09 21:25:41.120

Gotchas to watch out for

It is important to know that if an error occurs when the INSERT/DELETE/UPDATE statement runs the OUTPUT clause will still return rows.

This is true even if the statement is DML statement is rolled back. Therefore it can be prudent to check that no error has occurred before using the data returned.

You cannot use the clause when an INSERT statement contains an EXECUTE statement.

OUTPUT INTO cannot be used to insert rows into a view or rowset function.

For a full list of caveats and full syntax see the Microsoft Docs.

Conclusion

We have seen how the introduction of the OUTPUT clause in SQL Server 2005 gave the SQL developer a relatively straight forward way to access the data that has been modified by DML statements without needing to use a subsequent SELECT query.

We have learned how the OUTPUT clause uses the Inserted and Deleted in-memory tables to access the rows that are modified by DML statements (INSERT, UPDATE, DELETE and MERGE). We have seen an example of how this data can be either returned directly to the output window or calling application, or it can be inserted into various types of tables.

Join our newsletter

Subscribe to our free weekly newsletter and get exclusive SQL tips and articles straight to your inbox. 

 Your email address is only used to send you our newsletter.