The OUTPUT clause

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:

-- 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:

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

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

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.

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

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'

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:

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

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!