Some time ago, I wrote an article that used the OUTPUT clause with the MERGE statement. It was a more complicated example of using OUTPUT, so I feel that more basic examples are required, to reflect a more common scenario I frequently encounter.
Background
When data needs to be altered it is generally a good idea to cater for restoring that data to its original form. For example, if a change turns out to be incorrect or has had an unexpected impact, it may be necessary to roll the data back to the original state.
I frequently see examples where code has initially selected the data to be manipulated into a ‘rollback’ table, before manipulating the original data. This appears to be a good move in principle, but with a busy system anything can happen to the data between storing it and then playing with it. Your rollback might not recover everything.
Test Environment
Create a very basic test database. Two tables – one with the original data and another where the data will be copied to, so it can be rolled back if required.
Listing 1: The Test Database, Tables and Data
CREATE DATABASE Test;
GO
USE Test;
GO
CREATE TABLE dbo.Accounts
(
ID INT IDENTITY NOT NULL,
Surname NVARCHAR(50) NOT NULL,
Forename NVARCHAR(50) NOT NULL,
Suspended BIT NOT NULL
DEFAULT 0,
SuspendedDate DATE NULL
);
CREATE TABLE dbo.ActionedAccounts
(
ID INT IDENTITY,
Surname NVARCHAR(50),
Forename NVARCHAR(50),
Suspended BIT,
SuspendedDate DATE
);
GO
INSERT INTO dbo.Accounts
(
Surname,
Forename,
Suspended,
SuspendedDate
)
VALUES
(N'Nicolas', N'Max', 1, '01 Mar 2022'),
(N'Clifton', N'Jose', DEFAULT, NULL),
(N'Marci', N'Tonia', DEFAULT, NULL),
(N'Jessie', N'Christina', DEFAULT, NULL),
(N'Lance', N'Betty', DEFAULT, NULL),
(N'Eric', N'Cornelius', DEFAULT, NULL),
(N'Oliver', N'Adrienne', DEFAULT, NULL),
(N'Smith', N'Evan', 1, '20 April 2022'),
(N'Jones', N'Sonja', DEFAULT, NULL),
(N'Williams', N'Mike', DEFAULT, NULL),
(N'Grey', N'Jon', DEFAULT, NULL),
(N'Andrews', N'Darnell', 1, '15 Feb 2023'),
(N'Harrison', N'Angelo', DEFAULT, NULL),
(N'Heyes', N'Alfred', DEFAULT, NULL),
(N'Michaels', N'Karen', DEFAULT, NULL),
(N'Simons', N'Nora', 1, '12 Nov 2022'),
(N'Sinclair', N'Isabel', DEFAULT, NULL);
Examples
We’re now going to delete all rows where ‘Suspended’ is set to 1. Obviously, we want to store this information elsewhere, just in case we need to put it back. That is the purpose of the ‘ActionedAccounts’ table.
Copy the Data to a Rollback Table and Then Delete
This is the method that I usually see – identify the rows that need to be removed, copy them to a separate table and then remove them from the original table. At first glance, this sounds fine. However, SQL Server can perform a lot of actions in a very short space of time. What happens if, as you’re executing your code, an update is made to a row and you aren’t aware of it?
There are 17 rows of data in this table at this point in time. To keep it simple I’m just going to use one window to replicate this issue – setting up a separate update to run just makes the example more involved to set up. Use your imagination and look at the comments in the following code.
Listing 2: Two Phases for the Deletion
USE Test;
GO
/* I'm going to remove all rows where Suspended = 1.
Therefore, I'm first going to move those rows into another table,
in case I need to recover this data.*/
INSERT INTO [dbo].[ActionedAccounts]([ID], [Surname], [Forename], [Suspended], [SuspendedDate])
SELECT ID,
Surname,
Forename,
Suspended,
SuspendedDate FROM dbo.Accounts
WHERE Suspended = 1;
GO
SELECT COUNT(ID) FROM dbo.ActionedAccounts;
/*There are 4 rows in ActionedAccount*/
/*At this point, another process set another row's 'suspended' column to 1, and inserts the current date.
This would be another SPID, but we'll keep it simple and pretend.*/
UPDATE dbo.Accounts
SET Suspended = 1,
SuspendedDate = GETDATE()
WHERE Surname = 'Clifton';
GO
/*Next, I delete the rows that I believe I have safely copied off elsewhere*/
DELETE dbo.Accounts
WHERE Suspended = 1;
GO
/* That has just deleted 5 rows. I might be in a bit of trouble here.*/
And sure enough, if I need to roll my changes back there’ll be one row less in the original table.
Listing 3: Rollback to Restore the Data (in Theory)
USE Test;
GO
SET IDENTITY_INSERT dbo.Accounts ON;
INSERT INTO dbo.Accounts
(
ID,
Surname,
Forename,
Suspended,
SuspendedDate
)
SELECT ID,
Surname,
Forename,
Suspended,
SuspendedDate
FROM dbo.ActionedAccounts;
SET IDENTITY_INSERT dbo.Accounts OFF;
GO
SELECT COUNT(ID)
FROM dbo.Accounts;
/*I have 16 rows - one row less than when I started the change.
I might need update my resume*/
Fortunately, there is a way to delete the rows and copy the data elsewhere as one instruction, so this situation is no longer an issue.
Delete the Data and Copy it to a Rollback Table
By using the OUTPUT clause, we can combine the copy and the delete into one command. Other processes can still manipulate the same table, but any changes that we made are fully reversible.
First, we need to restore the data to our original state.
Listing 4: Truncate and Recreate the Data
TRUNCATE TABLE dbo.Accounts;
TRUNCATE TABLE dbo.ActionedAccounts;
GO
INSERT INTO dbo.Accounts
(
Surname,
Forename,
Suspended,
SuspendedDate
)
VALUES
(N'Nicolas', N'Max', 1, '01 Mar 2022'),
(N'Clifton', N'Jose', DEFAULT, NULL),
(N'Marci', N'Tonia', DEFAULT, NULL),
(N'Jessie', N'Christina', DEFAULT, NULL),
(N'Lance', N'Betty', DEFAULT, NULL),
(N'Eric', N'Cornelius', DEFAULT, NULL),
(N'Oliver', N'Adrienne', DEFAULT, NULL),
(N'Smith', N'Evan', 1, '20 April 2022'),
(N'Jones', N'Sonja', DEFAULT, NULL),
(N'Williams', N'Mike', DEFAULT, NULL),
(N'Grey', N'Jon', DEFAULT, NULL),
(N'Andrews', N'Darnell', 1, '15 Feb 2023'),
(N'Harrison', N'Angelo', DEFAULT, NULL),
(N'Heyes', N'Alfred', DEFAULT, NULL),
(N'Michaels', N'Karen', DEFAULT, NULL),
(N'Simons', N'Nora', 1, '12 Nov 2022'),
(N'Sinclair', N'Isabel', DEFAULT, NULL);
GO
Now, delete the rows where ‘suspended’ = 1. If another process alters another row, it can’t interfere with the Delete/Output execution and we don’t follow this with a further deletion command, because we have already done that.
Listing 5: Delete with Output
USE Test;
GO
/*Delete the rows where 'suspended' = 1
and output the deleted data to the ActionedAccounts table*/
DELETE dbo.Accounts
OUTPUT Deleted.ID,
Deleted.Surname,
Deleted.Forename,
Deleted.Suspended,
Deleted.SuspendedDate
INTO dbo.ActionedAccounts
(
[ID],
[Surname],
[Forename],
[Suspended],
[SuspendedDate]
)
WHERE Suspended = 1;
GO
/*Another process can't interrupt the command above,
it is all executed at once. So, if an alteration is made
from elsewhere - so what? I've already made my changes
and secured the data elsewhere.*/
UPDATE dbo.Accounts
SET Suspended = 1,
SuspendedDate = GETDATE()
WHERE Surname = 'Clifton';
GO
Now, if I repeat the rollback code in Listing 3, I have 17 rows at the end of it. All of the data that my code removed has been restored.
Updating Data with Output
The Output clause doesn’t have to be used when removing data – it can also be used when altering data.
Reset the test environment with Listing 4.
Then we change the surname of one of the rows, storing the previous values in the ‘ActionedAccount table. Again, this can be used to rollback the changes if required.
Listing 6: Altering Data with Output
USE Test;
GO
UPDATE dbo.Accounts
SET Surname = 'smith'
OUTPUT Deleted.ID,
Deleted.Surname,
Deleted.Forename,
Deleted.Suspended,
Deleted.SuspendedDate
INTO dbo.ActionedAccounts
(
[ID],
[Surname],
[Forename],
[Suspended],
[SuspendedDate]
)
WHERE ID = 1;
GO
SELECT *
FROM dbo.Accounts;
SELECT *
FROM dbo.ActionedAccounts;
We can see that we now have the updated row, plus the previous values, stored in the ActionedAccounts table.
Figure 1: Results from Listing 6
Saving the Before and After Values
In the previous examples, I showed the ‘deleted’ properties that the Output clause has access to. It is also possible to extract the ‘inserted’ values too, which means that a type of audit can be written, showing the changes made.
For this example, we’ll create a new audit table, just tracking the changes to names. Then the code will change the forename and surname of one row, displaying the results after execution.
Listing 7: Showing the Data Changes via Output
USE Test;
GO
CREATE TABLE dbo.AccountAudit
(
ID INT,
PreviousSurname NVARCHAR(50),
NewSurname NVARCHAR(50),
PreviousForename NVARCHAR(50),
NewForename NVARCHAR(50)
);
GO
UPDATE dbo.Accounts
SET Surname = 'Smith',
Forename = 'John'
OUTPUT Inserted.ID,
Deleted.Surname,
Inserted.Surname,
Deleted.Forename,
Inserted.Forename
INTO dbo.AccountAudit
(
[ID],
[PreviousSurname],
[NewSurname],
[PreviousForename],
[NewForename]
)
WHERE Surname = 'Clifton';
GO
SELECT * FROM dbo.Accounts;
SELECT * FROM dbo.AccountAudit;
Figure 2: Results from Listing 7
Conclusion
Of course, there are other ways to ensure that your changes don’t lose data and I’m sure I could think of several esoteric solutions if I tried hard enough.
But why not keep it simple?
There are a few things to watch out for. Instead Of Triggers is one example and the Microsoft documentation details these things quite well.
Notes on SQL 
One thought on “The OUTPUT Clause – Basic Examples”