Skip to content

The OUTPUT clause

  • by

Hey there! Welcome back to another post.

Today, I’m going to talk about something not a lot of people use and but some others do, and probably don’t quite understand.

The OUTPUT clause. Some people might have seen it before in the MERGE statement but not many people know that it can be used with the Data Modification Language (DML) such as INSERT, DELETE and UPDATE.

Its main use is to return information from inserted, updated or deleted rows and uses two special tables called INSERTED and DELETED, which are tables Triggers also use. Now, if you haven’t used triggers, stay tuned to my blog, I’m going to talk about them in future posts.

DO I NEED IT?

Well, it depends. If you have processes that audit your data, or to archive data, it can be useful to you, since apart from returning information back to the screen, you can also add an INTO clause and save your data to a table.

I’m going to show you a few examples, very basic on how to use it and later we can go into more “advanced” things to do (you’ll see it’s very easy to understand and use).

For this example, I’m using the Orders table in our TestDB we created in previous posts. If you don’t have it, please check my GitHub repository .

Suppose we are going to insert the orders for a certain customer into a temporary table, but we need to see if the rows we are inserting are correct. We can do this:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
-- Using the OUTPUT clause to return information to the user.
INSERT INTO #OrdersCustomerID(OrderID, CustomerID, OrderDate, ShipName, ShipAddress, ShipCity, ShipCountry, ShipPostalCode)
OUTPUT
INSERTED.OrderID, INSERTED.OrderDate, INSERTED.ShipName, INSERTED.ShipAddress, INSERTED.ShipCity, INSERTED.ShipCountry, INSERTED.ShipPostalCode
SELECT
OrderID
, CustomerID
, OrderDate
, ShipName
, ShipAddress
, ShipCity
, ShipCountry
, ShipPostalCode
FROM
dbo.Orders
WHERE
CustomerID = 'GROSR'
-- Using the OUTPUT clause to return information to the user. INSERT INTO #OrdersCustomerID(OrderID, CustomerID, OrderDate, ShipName, ShipAddress, ShipCity, ShipCountry, ShipPostalCode) OUTPUT INSERTED.OrderID, INSERTED.OrderDate, INSERTED.ShipName, INSERTED.ShipAddress, INSERTED.ShipCity, INSERTED.ShipCountry, INSERTED.ShipPostalCode SELECT OrderID , CustomerID , OrderDate , ShipName , ShipAddress , ShipCity , ShipCountry , ShipPostalCode FROM dbo.Orders WHERE CustomerID = 'GROSR'
-- Using the OUTPUT clause to return information to the user.
INSERT INTO #OrdersCustomerID(OrderID, CustomerID, OrderDate, ShipName, ShipAddress, ShipCity, ShipCountry, ShipPostalCode)
OUTPUT
	INSERTED.OrderID, INSERTED.OrderDate, INSERTED.ShipName, INSERTED.ShipAddress, INSERTED.ShipCity, INSERTED.ShipCountry, INSERTED.ShipPostalCode
SELECT 
	OrderID
,	CustomerID
,	OrderDate
,	ShipName
,	ShipAddress
,	ShipCity
,	ShipCountry
,	ShipPostalCode	
FROM 
	dbo.Orders
WHERE
	CustomerID = 'GROSR'

We know what CustomerID are going to insert, so we don’t display that column in the OUTPUT. 

Now, what should we do if for some reason, we want to insert those records into another table for auditing purposes? Let’s see:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
-- Using the OUTPUT clause and insert into a table.
INSERT INTO #OrdersCustomerID(OrderID, CustomerID, OrderDate, ShipName, ShipAddress, ShipCity, ShipCountry, ShipPostalCode)
OUTPUT
INSERTED.OrderID, INSERTED.CustomerID, INSERTED.OrderDate, INSERTED.ShipName, INSERTED.ShipAddress, INSERTED.ShipCity, INSERTED.ShipCountry, INSERTED.ShipPostalCode
INTO
TableForAuditingPurposes(OrderID, CustomerID, OrderDate, ShipName, ShipAddress, ShipCity, ShipCountry, ShipPostalCode)
SELECT
OrderID
, CustomerID
, OrderDate
, ShipName
, ShipAddress
, ShipCity
, ShipCountry
, ShipPostalCode
FROM
dbo.Orders
WHERE
CustomerID = 'GROSR'
-- Using the OUTPUT clause and insert into a table. INSERT INTO #OrdersCustomerID(OrderID, CustomerID, OrderDate, ShipName, ShipAddress, ShipCity, ShipCountry, ShipPostalCode) OUTPUT INSERTED.OrderID, INSERTED.CustomerID, INSERTED.OrderDate, INSERTED.ShipName, INSERTED.ShipAddress, INSERTED.ShipCity, INSERTED.ShipCountry, INSERTED.ShipPostalCode INTO TableForAuditingPurposes(OrderID, CustomerID, OrderDate, ShipName, ShipAddress, ShipCity, ShipCountry, ShipPostalCode) SELECT OrderID , CustomerID , OrderDate , ShipName , ShipAddress , ShipCity , ShipCountry , ShipPostalCode FROM dbo.Orders WHERE CustomerID = 'GROSR'
-- Using the OUTPUT clause and insert into a table.
INSERT INTO #OrdersCustomerID(OrderID, CustomerID, OrderDate, ShipName, ShipAddress, ShipCity, ShipCountry, ShipPostalCode)
OUTPUT
	INSERTED.OrderID, INSERTED.CustomerID, INSERTED.OrderDate, INSERTED.ShipName, INSERTED.ShipAddress, INSERTED.ShipCity, INSERTED.ShipCountry, INSERTED.ShipPostalCode
INTO
	TableForAuditingPurposes(OrderID, CustomerID, OrderDate, ShipName, ShipAddress, ShipCity, ShipCountry, ShipPostalCode)
SELECT 
	OrderID
,	CustomerID
,	OrderDate
,	ShipName
,	ShipAddress
,	ShipCity
,	ShipCountry
,	ShipPostalCode	
FROM 
	dbo.Orders
WHERE
	CustomerID = 'GROSR'

Very simple, just include an INTO clause and the name of the table where you want to insert your rows to (I know it probably doesn’t make sense to insert “good” rows to a temporary table and have a physical table for auditing, but just play along 😛 ). Also, always remember to define the columns to insert or you will have an error message to remind you.

DELETE WITH OUTPUT

Now, to use the OUTPUT clause with the DELETE statement, we cannot use the INSERTED special table given that there are no rows to insert. Instead, we will use the DELETED table.

For this example, I’m going to be using the same Orders table, and the auditing table I already created. Let’s delete the records for another CustomerID.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
-- OUTPUT with DELETE statement, and inserting into a table
DELETE dbo.Orders
OUTPUT
DELETED.OrderID, DELETED.CustomerID, DELETED.OrderDate, DELETED.ShipName, DELETED.ShipAddress, DELETED.ShipCity, DELETED.ShipCountry, DELETED.ShipPostalCode
INTO
TableForAuditingPurposes(OrderID, CustomerID, OrderDate, ShipName, ShipAddress, ShipCity, ShipCountry, ShipPostalCode)
WHERE
CustomerID = 'SAVEA'
-- OUTPUT with DELETE statement, and inserting into a table DELETE dbo.Orders OUTPUT DELETED.OrderID, DELETED.CustomerID, DELETED.OrderDate, DELETED.ShipName, DELETED.ShipAddress, DELETED.ShipCity, DELETED.ShipCountry, DELETED.ShipPostalCode INTO TableForAuditingPurposes(OrderID, CustomerID, OrderDate, ShipName, ShipAddress, ShipCity, ShipCountry, ShipPostalCode) WHERE CustomerID = 'SAVEA'
-- OUTPUT with DELETE statement, and inserting into a table
DELETE dbo.Orders
OUTPUT
	DELETED.OrderID, DELETED.CustomerID, DELETED.OrderDate, DELETED.ShipName, DELETED.ShipAddress, DELETED.ShipCity, DELETED.ShipCountry, DELETED.ShipPostalCode
INTO
	TableForAuditingPurposes(OrderID, CustomerID, OrderDate, ShipName, ShipAddress, ShipCity, ShipCountry, ShipPostalCode)
WHERE
	CustomerID = 'SAVEA'

Same pattern, the OUTPUT clause with the columns you want to see and the name of the table to insert those deleted rows. See the message that 27 rows were affected, let’s check if we have those in our auditing table, and also if they were deleted from the Orders table.

There you go. 27 rows from the auditing table, and zero rows from the Orders table.

WHAT ABOUT THE UPDATE STATEMENT?

Since we have the INSERTED and DELETED special tables, do we have an UPDATED table as well? The answer is, NO.

When doing an update to a table we will have to use BOTH, INSERTED and DELETED since we’re basically performing both operations. We are inserting new values into the column (or columns) and we are deleting old values from it.

For a better understanding, let’s see an example on how to do it, this one will only display information, it won’t insert anything anywhere.

We are going to update the ShippedDate for all the orders from a CustomerID.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
-- Using OUTPUT clause with UPDATE statement
UPDATE dbo.Orders
SET
ShippedDate = GETDATE()
OUTPUT
INSERTED.OrderID
, DELETED.ShippedDate AS Old_ShippedDate
, INSERTED.ShippedDate AS New_ShippedDate
WHERE
CustomerID = 'HUNGC'
-- Using OUTPUT clause with UPDATE statement UPDATE dbo.Orders SET ShippedDate = GETDATE() OUTPUT INSERTED.OrderID , DELETED.ShippedDate AS Old_ShippedDate , INSERTED.ShippedDate AS New_ShippedDate WHERE CustomerID = 'HUNGC'
-- Using OUTPUT clause with UPDATE statement
UPDATE dbo.Orders
SET
	ShippedDate = GETDATE()
OUTPUT
	INSERTED.OrderID
,	DELETED.ShippedDate AS Old_ShippedDate
,	INSERTED.ShippedDate AS New_ShippedDate
WHERE
	CustomerID = 'HUNGC'

We updated the ShippedDate of all five orders for that CustomerID, see how I used the DELETED and INSERTED tables, also see how I was able to define aliases for the old and new values of the column.

When you need to insert those records into a table for audit, is basically the same operation as we’ve been doing in the previous examples, with the difference that now, we’re going to have two values for the same column (an old value, and a new value), so we need to make a little adjustment to our auditing table. This is the way I learned and this is how I like doing it, but feel free to do the way it better suits your needs.

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
DECLARE @batch_date DATETIME = GETDATE()
UPDATE dbo.Orders
SET
ShippedDate = GETDATE()
OUTPUT
inserted.OrderID
, inserted.CustomerID
, inserted.OrderDate
, deleted.ShippedDate as ShippedDate_Old
, inserted.ShippedDate as ShippedDate_New
, @batch_date
INTO
AuditTableForUpdates(OrderID, CustomerID, OrderDate, ShippedDate_Old, ShippedDate_New, BatchDate)
WHERE
CustomerID = 'SIMOB'
DECLARE @batch_date DATETIME = GETDATE() UPDATE dbo.Orders SET ShippedDate = GETDATE() OUTPUT inserted.OrderID , inserted.CustomerID , inserted.OrderDate , deleted.ShippedDate as ShippedDate_Old , inserted.ShippedDate as ShippedDate_New , @batch_date INTO AuditTableForUpdates(OrderID, CustomerID, OrderDate, ShippedDate_Old, ShippedDate_New, BatchDate) WHERE CustomerID = 'SIMOB'
DECLARE @batch_date DATETIME = GETDATE()

UPDATE dbo.Orders
SET
	ShippedDate = GETDATE()
OUTPUT
	inserted.OrderID
,	inserted.CustomerID
,	inserted.OrderDate
,	deleted.ShippedDate as ShippedDate_Old
,	inserted.ShippedDate as ShippedDate_New
,	@batch_date
INTO
	AuditTableForUpdates(OrderID, CustomerID, OrderDate, ShippedDate_Old, ShippedDate_New, BatchDate)
WHERE
	CustomerID = 'SIMOB'
7 records inserted into the audit table with the old and new values, 7 records updated in original table.

Our records were updated and inserted with the old and new values into the auditing table. Notice something in the UPDATE statement? I declared a variable called @batch_date and assigned it today’s date. I did this because, if we mess up and update the wrong values, using the auditing table and the original table, we can join them using the primary key column (or columns) and the BatchDate and return the values to the original state.

Like this:

Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
-- Returning the values of the OrderIDs for this Customer to their original values.
DECLARE @batch_date DATETIME = '2019-09-24 22:59:08.280'
UPDATE O
SET
O.ShippedDate = A.ShippedDate_Old
OUTPUT
inserted.OrderID
, deleted.ShippedDate AS ShippedDate_Old
, inserted.ShippedDate AS ShippedDate_New
FROM
dbo.Orders AS O
INNER JOIN dbo.AuditTableForUpdates as A
ON A.OrderID = O.OrderID
WHERE
A.BatchDate = @batch_date
AND A.CustomerID = 'SIMOB'
-- Returning the values of the OrderIDs for this Customer to their original values. DECLARE @batch_date DATETIME = '2019-09-24 22:59:08.280' UPDATE O SET O.ShippedDate = A.ShippedDate_Old OUTPUT inserted.OrderID , deleted.ShippedDate AS ShippedDate_Old , inserted.ShippedDate AS ShippedDate_New FROM dbo.Orders AS O INNER JOIN dbo.AuditTableForUpdates as A ON A.OrderID = O.OrderID WHERE A.BatchDate = @batch_date AND A.CustomerID = 'SIMOB'
-- Returning the values of the OrderIDs for this Customer to their original values.
DECLARE @batch_date DATETIME = '2019-09-24 22:59:08.280'

UPDATE O
SET
	O.ShippedDate = A.ShippedDate_Old
OUTPUT
	inserted.OrderID
,	deleted.ShippedDate AS ShippedDate_Old
,	inserted.ShippedDate AS ShippedDate_New
FROM 
	dbo.Orders AS O
	INNER JOIN dbo.AuditTableForUpdates as A
		ON A.OrderID = O.OrderID
WHERE
	A.BatchDate = @batch_date
AND A.CustomerID = 'SIMOB'
Records rolled back to their original value.

I’ve been working with this approach since a while ago, and it’s been really useful for those moments when the light’s on, but no one’s home.

Anyhoo, this is the end for today, I hope you liked the post.

Also, remember that you can find the code for this and other posts in the link below:
https://github.com/sqldevmty/sql-code/tree/development

If you have any question or comment, shoot me an email, leave a message or tweet me here.

Don’t forget to like and share. See you soon. Cheers!


Discover more from Ben Rodríguez

Subscribe to get the latest posts sent to your email.

Discover more from Ben Rodríguez

Subscribe now to keep reading and get access to the full archive.

Continue reading

Buy Me A Coffee
Thank you for visiting. You can now buy me a beer!