Simple Talk is now part of the Redgate Community hub - find out why

Implementing the OUTPUT Clause in SQL Server 2008

In retrospect, it was probably the inclusion of the OUTPUT clause in the MERGE statement that gave SQL Server 2008 its most powerful SQL enhancement.. It isn't the easiest of features to explain, but Bob does it in his usual clear and careful way.

In SQL Server 2005 and 2008, you can add an OUTPUT clause to your data manipulation language (DML) statements. The clause returns a copy of the data that you’ve inserted into or deleted from your tables. You can return that data to a table variable, a temporary or permanent table, or to the processing application that’s calls the DML statement. You can then use that data for such purposes as archiving, confirmation messaging, or other application requirements.

Both SQL Server 2005 and 2008 let you add an OUTPUT clause to INSERT, UPDATE, or DELETE statements. SQL Server 2008 also lets you add the clause to MERGE statements. In this article, I demonstrate how to include an OUTPUT clause in each one of these statements. I developed the examples used for the article on a local instance of SQL Server 2008. The examples also work in SQL Server 2005, except for those related to the MERGE statement.

If you want to try the examples in this article, you should first run the following script to create the Books table in a SQL Server database:

The examples are all performed against the Books table. If you run the statements, do so in the order presented in the article, if you want your results to match the results shown here.

Using an OUTPUT Clause in an INSERT Statement

When you insert data into a table, you can use the OUTPUT clause to return a copy of the data that’s been inserted into the table. The OUTPUT clause takes two basic forms: OUTPUT and OUTPUT INTO. Use the OUTPUT form if you want to return the data to the calling application. Use the OUTPUT INTO form if you want to return the data to a table or a table variable.

For most of these examples, I return the outputted data only to a table variable. The process for returning data to a table is the same as that for a variable. If in running these examples, you prefer to return the data to a table instead of a variable, simply create a table with the same columns as the variable, then specify the table rather than the variable in the OUTPUT clause.

NOTE: Returning data to the processing application is even simpler than returning it to a table or variable. As we work through the first example, I’ll point out how that’s done.

In the first example, I declare a table variable named @InsertOutput1 with the same column structure as the Books table. I then insert a row into the Books table and output the inserted data to the variable:

As you can see in the INSERT statement, the OUTPUT clause is placed between the INSERT and VALUES clause. Because I’m outputting the data to a variable, the OUTPUT clause includes two parts: the OUTPUT subclause and the INTO subclause.

The OUTPUT subclause includes the keyword INSERTED, followed a period and asterisk. The keyword is a column prefix that specifies which values to output. The asterisk indicates that all column values inserted into the Books table (BookID, BookTitle, ModifiedDate) should be outputted to the variable.

The INTO subclause defines the table or table variable that the OUTPUT clause targets. In this case, the target is the @InsertOutput1 variable. If you want to return the information to the calling application, rather than the variable (or a table), you simply omit the INTO subclause.

That’s all there is to defining an OUTPUT clause. After the INSERT statement, I include a couple SELECT statements to view the contents of the table and the variable. Because this is the first row inserted into the table, the results returned by both statements are the same, as shown in the following table:

BookID

BookTitle

ModifiedDate

101

One Hundred Years of Solitude

2010-07-17 12:21:31.300

As you can see, the values that were inserted into the Books table are also returned to the @InsertOutput1 variable. You could just as easily have saved the information to a table. Or you could have eliminated the INTO subclause and sent the data to the processing application.

Now let’s look at another example. In the next set of statements, I first declare the @InsertOutput2 table variable with the same column structure as the Books table. Then I insert a second row into the Books table and output the values to the new variable:

The primary difference between this INSERT statement and the one in the previous example as that I now use the INSERTED column prefix in the OUTPUT subclause to specify each column (rather than using the asterisk). Notice that I’ve separated the INSERTED column specifications with commas.

Because I inserted a second row into the Books table, the first SELECT statement now returns two rows, as shown in the following results:

BookID

BookTitle

ModifiedDate

101

One Hundred Years of Solitude

2010-07-17 12:21:31.300

102

Pride and Prejudice

2010-07-17 12:23:22.100

However, because I’m inserting only one row into the Books table, the @InsertOutput2 variable contains only one row. As a result, the second SELECT statement returns only one row, as shown in the following table:

BookID

BookTitle

ModifiedDate

102

Pride and Prejudice

2010-07-17 12:23:22.100

In some cases, you don’t want to output all inserted values, so you must define your OUTPUT clause accordingly. In the next example, I output only the BookID and BookTitle columns:

As before, I first declare a table variable, but this one includes only the BookID and BookTitle columns. Next, I define an INSERT statement, which includes an OUTPUT clause that reflects that only two columns will be outputted. That means, in the OUTPUT subclause, I add only two INSERTED column prefixes and specify the BookID and BookTitle columns.

When you run the example, the first SELECT statement, as expected, returns the three rows shown in the following table:

BookID

BookTitle

ModifiedDate

101

One Hundred Years of Solitude

2010-07-17 12:21:31.300

102

Pride and Prejudice

2010-07-17 12:23:22.100

103

The Great Gatsby

2010-07-17 12:25:25.067

However, the second SELECT statement returns only the BookID and BookTitle columns of the newly inserted row:

BookID

BookTitle

103

The Great Gatsby

Now let’s look at another variation on the OUTPUT clause. Once more, I define a table variable (@InsertOutput4) that includes three columns, but this time the columns have different names and are in a different order:

Because the target column names and order don’t match the Books table, I add the target column names to the INTO subclause-enclosed in parentheses and separated by commas. The order of the target column names must also correspond to the order in which the columns are being inserted and outputted. As a result, the BookID column is matched to the TitleID column, BookTitle to Title, and ModifiedDate to TitleAddDate.

I then add the SELECT statements to verify the results. The first SELECT statement returns all four rows:

BookID

BookTitle

ModifiedDate

101

One Hundred Years of Solitude

2010-07-17 12:34:16.467

102

Pride and Prejudice

2010-07-17 12:34:24.580

103

The Great Gatsby

2010-07-17 12:34:38.330

104

Mrs. Dalloway

2010-07-17 12:36:10.237

The second SELECT statement returns only the newly inserted row, with the columns names and values as they appear in the @InsertOutput4 variable:

Title

TitleID

TitleAddDate

Mrs. Dalloway

104

2010-07-17 12:36:10.237

You should now have a good sense of how to use an OUTPUT clause in an INSERT statement. Note, however, that most of the principles explained here apply to other DML statements as well, so let’s move on to the UPDATE statement.

Using an OUTPUT Clause in an UPDATE Statement

In the previous examples, the OUTPUT clause includes the INSERTED column prefix in the OUTPUT subclause. However, the OUTPUT clause supports a second column prefix-DELETED. The DELETED prefix returns the values that have been deleted from a table.

This is important because an UPDATE operation is actually two operations-a deletion and an insertion. As a result, you use both the INSERTED and DELETED column prefixes when adding an OUTPUT clause to an UPDATE statement. Let’s look at a few examples to demonstrate how this works.

In the following series of statements, I declare the @UpdateOutput1 variable, delete a row from the Books table, and view the results of that process:

Notice first that the @UpdateOutput1 variable is a bit different from the table variables defined in earlier examples. In this case, we’re capturing the values associated with the update operations-those values that are deleted and those that are inserted. Because I plan to update the BookID and ModifiedDate columns, I want to preserve both the old and new values, along with the BookTitle column, which is not being updated.

After I create the @UpdateOutput1 variable, I define the UPDATE statement. Note that I’ve inserted an OUTPUT clause between the SET and WHERE clauses and that the OUTPUT subclause contains both DELETED and INSERTED column prefixes, separated by commas. I include a DELETED and INSERTED column prefix for each of the two columns I’m updating and an INSERTED column prefix for the column that will remain unchanged. The INTO subclause specifies the table variable, as you saw in previous examples.

When you run the first SELECT statement, the Books table should reflect the modified values, as shown in the following results:

BookID

BookTitle

ModifiedDate

101

One Hundred Years of Solitude

2010-07-17 12:34:16.467

102

Pride and Prejudice

2010-07-17 12:34:24.580

103

The Great Gatsby

2010-07-17 12:34:38.330

105

Mrs. Dalloway

2010-07-17 12:36:10.237

As you can see, the BookID and ModifiedDate values have changed for the Mrs. Dalloway row. The second SELECT statement shows the specifics of the modifications, as they’ve been returned to the @UpdateOutput1 variable:

OldBookID

NewBookID

BookTitle

OldModifiedDate

NewModifiedDate

104

105

Mrs. Dalloway

2010-07-17 13:09:10.580

2010-07-17 13:09:39.683

When you specify the column prefixes in your OUTPUT subclause, you can create an expression that uses the prefixes to calculate a value. For instance, in the following example, I calculate the difference between the old and new ModifiedDate values:

The example is different from the previous example in a couple ways. First, the table variable includes an extra field (DiffInSeconds) to show the difference in seconds between the two modified dates. Next, the OUTPUT subclause includes an additional column definition in which the DATEDIFF function is used to calculate the difference between the deleted and inserted modified dates.

Once again, the first SELECT statement returns the content of the Books table, after the last row has been modified:

BookID

BookTitle

ModifiedDate

101

One Hundred Years of Solitude

2010-07-17 12:34:16.467

102

Pride and Prejudice

2010-07-17 12:34:24.580

103

The Great Gatsby

2010-07-17 12:34:38.330

106

Mrs. Dalloway

2010-07-17 12:36:10.237

The results from the second SELECT statement show the DiffInSeconds column, which reflects the difference in seconds between the two updates:

OldBookID

NewBookID

BookTitle

OldModifiedDate

NewModifiedDate

DiffInSeconds

105

106

Mrs. Dalloway

2010-07-17 13:20:34.787

2010-07-17 13:21:26.753

52

Once you understand how to use the OUTPUT clause in an UPDATE statement, you can see that you have all the elements necessary to add an OUTPUT clause to a DELETE statement, so let’s look at how that works.

Using an OUTPUT Clause in a DELETE Statement

In a DELETE statement, you add the OUTPUT clause between the DELETE and WHERE clauses. The following set of statements declare the @DeleteOutput1 table variable, delete a row from the Books table, and verifies the deletion:

There’s nothing new about the @DeleteOutput1 table variable. I declare it with the same table structure used in the Books table, as you saw in previous examples.

In the DELETE statement, the OUTPUT subclause uses the asterisk with the DELETED column prefix to indicate that all columns should be outputted to the @DeleteOutput1 variable. The first SELECT statement returns the following results:

BookID

BookTitle

ModifiedDate

101

One Hundred Years of Solitude

2010-07-17 13:09:10.573

102

Pride and Prejudice

2010-07-17 13:09:10.577

103

The Great Gatsby

2010-07-17 13:09:10.580

As you can see, the last row has been deleted from the table. The second SELECT statement confirms that the deleted row has been returned to the @DeleteOutput1 variable:

BookID

BookTitle

ModifiedDate

106

Mrs. Dalloway

2010-07-17 13:21:26.753

The next example is similar to the last except that I specify the column prefixes in the OUTPUT subclause, rather than using as asterisk:

As the example shows, an OUTPUT clause in a DELETE statement is similar to one in an INSERT or UPDATE statement. The key is to ensure that you’ve specified your column prefixes correctly. Not surprisingly, the first SELECT statement verifies that another row has been deleted from the Books table:

BookID

BookTitle

ModifiedDate

101

One Hundred Years of Solitude

2010-07-17 13:09:10.573

102

Pride and Prejudice

2010-07-17 13:09:10.577

The deleted row has been deleted has been added to the @DeleteOutput2 variable, as shown in the results from the second SELECT statement:

BookID

BookTitle

ModifiedDate

103

The Great Gatsby

2010-07-17 13:09:10.580

You’re not limited to one OUTPUT clause in a DML statement. For instance, there might be times when you want to output the results to the processing application as well as to a variable or table. In such a case, you can include multiple clauses. The following example is similar to the previous one, except that I’ve added another OUTPUT clause:

The first OUTPUT clause is the same as the last example. However, the second OUTPUT clause is new. Notice that the clause does not include an INTO subclause. This indicates that the outputted data will be sent to the calling application. Also notice that the set of column prefixes is different in the two OUTPUT clauses. The prefixes can be the same or different.

If you were to run the statements in this example after running the previous example, no data will be sent to the variable or to the calling application because that row has already been deleted. However, if the row exists, the appropriate data would be sent to both targets.

Using an OUTPUT Clause in a MERGE Statement

When working in SQL Server 2008, you can add an OUTPUT clause to a MERGE statement. The process is similar to adding the clause to an UPDATE statement; you use both the INSERTED and DELETED column prefixes.

Let’s look at an example that demonstrates how this work. First, however, we must create a second table to support the MERGE statement. The following script creates the Book2 table and populates it with two rows:

For the @MergeOutput1 variable, I include columns for both the old and new titles. I also include a column named ActionType. The column reflects the type of modification that is performed on the table when the rows are merged.

Now let’s look at the OUTPUT subclause. Notice that, in addition to the INSERTED and DELETED column prefixes, I include the $action variable. The built-in parameter returns one of three nvarchar(10) values-INSERT, UPDATE, or DELETE-and is available only to the MERGE statement. The value returned depends on the action performed on the row.

The first two SELECT statements return the data in the Books and Books2 tables after the MERGE. The results, which are identical, are shown in the following table:

BookID

BookTitle

ModifiedDate

101

One Hundred Years of Solitude

2010-07-17 13:09:10.573

102

Pride and Prejudice

2010-07-17 13:09:10.577

The results from the third SELECT statement, which calls the @MergeOutput1 variable, reflect the modifications made to the Books2 table:

ActionType

BookID

OldBookTitle

NewBookTitle

ModifiedDate

UPDATE

101

100 Years of Solitude

One Hundred Years of Solitude

2010-07-17 15:54:11.580

UPDATE

102

Pride & Prejudice

Pride and Prejudice

2010-07-17 15:54:11.580

Whether you use want to output your data modifications to a processing application, a table, or a variable, the OUTPUT clause is an easy, effective way to capture that data. By using the clause, you can reduce the number of calls to the database, eliminate the need for auditing triggers, archive modified data, or pass data on to an application for further processing. The OUTPUT clause is a flexible and easy to use-and it offers big benefits. For further information on the clause, see the topic “OUTPUT Clause (Transact-SQL)” in SQL Server Books Online.

1095-output%20clause.png

The railroad diagram for the OUTPUT clause is provided as a PDF file below.

How you log in to Simple Talk has changed

We now use Redgate ID (RGID). If you already have an RGID, we’ll try to match it to your account. If not, we’ll create one for you and connect it.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue

Simple Talk now uses Redgate ID

If you already have a Redgate ID (RGID), sign in using your existing RGID credentials. If not, you can create one on the next screen.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue