When you are inserting, updating, or deleting records from a table, SQL Server keeps track of the records that are changed in two different pseudo tables: INSERTED, and DELETED. These tables are normally used in DML triggers. If you use the OUTPUT clause on an INSERT, UPDATE, DELETE or MERGE statement you can expose the records that go to these pseudo tables to your application and/or T-SQL code. In this article I will show you different ways to use the OUTPUT clause to obtain the information in these two pseudo tables.
Using the Output Clause on INSERT Statement
When you use the OUTPUT clause on an INSERT statement, the inserted column values can be returned to the calling application and/or your T-SQL code. To show you how this works let me first create an empty Book table that can be used to insert records. The code in Listing 1 can be used to create the Book table.
USE tempdb; GO CREATE TABLE Book ( Title varchar(100) NOT NULL, Author varchar(100) NOT NULL, PaperbackCheck char(3) NOT NULL DEFAULT 'NO', Pages int NULL);
Listing 1: Code to create Book table
In order to demonstrate the use of the OUTPUT clause I will insert one row into this table using the code in Listing 2.
INSERT INTO Book (Title, Author, Pages)
OUTPUT INSERTED.*
VALUES('The Da Vinci Code','Dan Brown',597);
Listing 2: Using the OUTPUT clause on an INSERT statement
The code in Listing 2 inserts a single row into the Book table and uses the OUTPUT clause so inserted column values can be returned to my application. In my code, after the OUTPUT clause I used the “INSERTED.*” notation to telling SQL Server to return every inserted column value to my application. When I run the code in Listing 2 from a query window inside of SQL Server Management Studio I get the results shown in Report 1in the results pane.
Title Author PaperbackCheck Pages -------------------------------------- ----------------- -------------- ----------- The Da Vinci Code Dan Brown NO 597
Report 1: Results from running code in Listing 2.
By looking at the column values returned to my results pane in Report 1, you will see a value for every column in my Book table. Note that I even got a value for the PaperbackCheck, which was a column I didn’t have in my INSERT statement. By using the OUTPUT clause you can now get values for all columns that have a column constraint, like identity values, or in my case in Listing 2 the one with a default constraint.
When using the OUTPUT clause, like I did in Listing 2, which returns the inserted column data to the calling application, you are not able to have a trigger on your table. If you need to have a trigger then you will need to use the OUTPUT clause in conjunction with the INTO option, as I have done in Listing 3.
ALTER TABLE BOOK ADD Id INT NOT NULL IDENTITY;
GO
DECLARE @Inserted TABLE(
Id int,
Title varchar(100),
Author varchar(100));
INSERT INTO Book
OUTPUT INSERTED.Id,
INSERTED.Title,
INSERTED.Author
INTO @Inserted
VALUES('To Kill a Mocking Bird','Harper Lee','YES',323),
('The Catcher in the Rye','J.D. Salinger','Yes',277),
('Cutting for Stone','Abraham Verghese','No',541),
('The Great Gatsby','F. Scott Fitzgerald','Yes',180),
('1984',' George Orwell','Yes',298),
('Harry Potter and the Sorcerer''s Stone','J.K. Rowling','Yes',310);
SELECT * FROM @Inserted;
Listing 3: Using the INTO clause to populate a table variable
When I run the code in Listing 3 I get the output in Report 2.
Id Title Author ----- ----------------------------------- -------------------------------------- 2 To Kill a Mocking Bird Harper Lee 3 The Catcher in the Rye J.D. Salinger 4 Cutting for Stone Abraham Verghese 5 The Great Gatsby F. Scott Fitzgerald 6 1984 George Orwell 7 Harry Potter and the Sorcerer's Stone J.K. Rowling
Report 2: Output returned when running code in Listing 3
If you look at the code in Listing 3, I did a couple of different things. First I added an identity column to my Book Table. Next I declared a table variable named @Inserted, which I followed up by inserting two rows into my table, and then lastly I selected the rows from my @Inserted table. By looking at the INSERT statement you will see I used the INTO clause to insert only specific columns from the INSERTED pseudo table into my table variable @Inserted. Additionally you should note that one of those columns was my identity column. By having the identity column values I could then use these values, if my application needed them.
Using the OUTPUT Clause on a DELETE Statement
Using the OUTPUT clause on a DELETE statement is very similar to using the OUTPUT clause on an INSERT statement. The one difference to note is the name of the pseudo table when deleting records is DELETED. Other than that, everything else is about the same. Let me go through a couple of examples so you can fully understand how to use the OUTPUT clause on a DELETE statement.
In my first example I want to return all of the column values for every record deleted, and I want those values returned to my calling program; to do that I can run the code in Listing 4.
DELETE Book OUTPUT DELETED.* WHERE Id = 1;
Listing 4: Return all deleted column values to calling application
When I run this code I get the output in Report 3 returned to my calling program.
Title Author PaperbackCheck Pages Id ----------------------- --------------------- -------------- ----------- ------ The Da Vinci Code Dan Brown NO 597 1
Report 3: Output returned when running code in Listing 4.
The code in Listing 4 only deleted 1 record. Therefore only one record was returned to my calling program. The information returned contained all the different column values for the deleted record.
You can also specify the columns you want return from the DELETED pseudo table, similar to what we did with the INSERT statement in Listing 3. In Listing 5 I demonstrate how to return only two column values from the DELETED pseudo table.
DELETE Book OUTPUT DELETED.Title, DELETED.Id WHERE Id > 2 and Id < 5;
Listing 5: Outputting only the Title column to the DELETED pseudo table
When I run the code in Listing 5 two rows will be deleted, those rows that have an Id value of 3 and 4. But since this code specifically identified the Title and Id columns those are the only column values returned to my calling program.
Using the OUTPUT Clause on a UPDATE Statement
Just like the INSERT and DELETE statement you can also use the OUTPUT clause on the UPDATE statement. Using the OUTPUT clause on an UPDATE statement allows you to return column values from both the DELETED and INSERTED pseudo tables. To demonstrate this, let me run the code in listing 6, which will update a single row.
UPDATE Book SET Pages = 201 OUTPUT DELETED.*, INSERTED.* WHERE Id = 5;
Listing 6: Using the OUTPUT clause on an UPDATE statement
Running the code in Listing 6 from a SQL Server Management Studio query window will return a single row of output that contains all of the columns from the DELETED and INSERTED pseudo tables, where the DELETED table column values will be displayed first, followed by the INSERTED table column values.
By having both the DELETED and INSERTED pseudo table information available you can build an audit table that can contain before and after images of column values. To do this you would use the INTO option of the OUTPUT clause. To demonstrate how to build an audit table let me run the code Listing 7.
DECLARE @AuditLog TABLE ( UpdateID int, DeletedPages int, UpdatedPages int) UPDATE Book SET Pages = 301 OUTPUT INSERTED.Id, DELETED.Pages, INSERTED.Pages INTO @AuditLog WHERE Id = 7; SELECT * FROM @DELETEDINSERTED;
Listing 7: Building an Audit table using the INTO option of the OUTPUT clause
In Listing 7 I first created my audit table, which in this case is a table variable name @DELETEDINSERTED. This table will capture the before and after images of an updated record. Since my UPDATE statement is only updating the Pages column, my audit table will only contain the before and after values of the Pages column. If you look at the UPDATE statement in Listing 7 you will see I included an OUTPUT clause that identified only three columns to be outputted: INSERTED.Id, DELETED.Pages, and INSERTED.Pages. Since this OUTPUT clause also contained the INTO option, I will get the before and after images of the Pages column placed into my table variable @AuditLog. If you run this code you will see that the SELECT statement at the end of the code in Listing 7 confirms that the before and after images of the Page column is captured in my @AuditLog table.
Using the OUTPUT Clause on a MERGE Statement
The MERGE statement also supports the OUTPUT clause as well. By using the OUPUT clause with an INTO option on the MERGE statement you can create an audit trail to show which records were updated, inserted or deleted. To demonstrate how this works look at the code in Listing 8 that will perform a MERGE statement that will update one record and insert another.
DECLARE @AuditLog2 Table (
Action varchar(10),
BeforeTitle varchar(100),
BeforeAuthor varchar(100),
BeforePaperBackBookCheck char(3),
BeforePages int,
BeforeId int,
AfterTitle varchar(100),
AfterAuthor varchar(100),
AfterPaperBackBookCheck char(3),
AfterPages int,
AfterId int)
MERGE INTO Book AS Target
USING (VALUES ('The Great Gatsby','F. Scott Fitzgerald','Yes',191),
('Lord of the Flies','William Golding','NO',182))
AS Source (NewTitle, NewAuthor, NewPaperBackCheck, NewPages)
ON Target.Title = Source.NewTitle
WHEN MATCHED THEN
UPDATE SET Pages = Source.NewPages
WHEN NOT MATCHED BY TARGET THEN
INSERT VALUES (NewTitle, NewAuthor,NewPaperBackCheck, NewPages)
OUTPUT $action,DELETED.*,INSERTED.* INTO @AuditLog2;
SELECT * FROM @AuditLog2;
Listing 8: Sample MERGE that uses OUTPUT clause
In my Listing 8 example I used the OUTPUT clause to identify whether or not an INSERT or UPDATE action was performed against my target table. I do this by outputting the $action value. Additionally I output all the columns in the DELETED and INSERTED pseudo tables to a table variable name @AuditLog2. If you run this code you will see the SELECT statement will display rows from my @AuditLog2 that will confirm that one row was inserted and one row was updated.
Limitation to Consider When Using the OUTPUT clause
There are a number of limitations you need to consider if you are planning on using the OUTPUT clause. Here is a partial list of those limitations:
- If you want to have triggers on your table then you will need to use the INTO option of the OUTPUT clause.
- When multiple rows are returned via an OUTPUT clause they will be returned in no particular order.
- The OUTPUT clause cannot be used in local partitioned views, distributed partitions, or remote tables.
- Cannot use the OUTPUT clause in an INSERT statement uses the EXECUTE clause.
- Cannot use the INTO option of the OUTPUT clause to insert into a view or rowset function.
For a complete list of limitation refer to Books Online.
Knowing What Was Inserted, Updated, and/or Deleted
If your application needs to know what column values where inserted, updated or deleted against your tables, then the OUTPUT clause is a great way to do this. By using the OUTPUT clause your application can take the values returned to perform additional logic. Being able to selectively decide which columns you want to OUTPUT provides you the flexibility to determine what kind of audit trail you want the OUTPUT clause to produce. Next time you are considering auditing inserted, updated, or deleted records, consider if the OUTPUT clause will provide you the functionality you need without creating a trigger.
