Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted March 2, 2012

Using SQL Server's Output Clause

By Greg Larsen

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.

See all articles by Greg Larsen



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date