In my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code that is written in the pre 2005 style and people still keep using those functions, procs and statements even though SQL Server 2005 and 2008 have much better functionality.

Today we are going to take a look at the OUTPUT clause as part of an INSERT, DELETE or UPDATE statement

In yesterday’s post SQL Advent 2011 Day 10: Upsert by using the Merge statement I already showed the OUTPUT clause in one or two examples, in today’s post I will only focus on the OUTPUT clause

First we need to create a test table

CREATE TABLE TestOutput (id int identity not null,SomeCol datetime)
GO

OUTPUT clause and insert statements

This code uses a Common Table Expression combined with the ROW_NUMBER function to generate 10 rows. We then use the dateadd function to generate 10 different days. We insert these 10 rows into the TestOutput output table, this table has an identity column and the id will be generated by SQL Server. We use the output statement to return the id as well as the SomeCol column values. The nice thing with the OUTPUT clause compared to the SCOPE_IDENTITY function is that the OUTPUT clause will return all values, SCOPE_IDENTITY only returns the last value generated

Run this code to see how it works

;WITH cte AS(
SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY name) AS number
from sysobjects)


INSERT TestOutput
OUTPUT inserted.id,inserted.SomeCol
SELECT DATEADD(d,number,'20110101')
FROM CTE

Output

id      SomeCol
--      -----------------------
1	2011-01-02 00:00:00.000
2	2011-01-03 00:00:00.000
3	2011-01-04 00:00:00.000
4	2011-01-05 00:00:00.000
5	2011-01-06 00:00:00.000
6	2011-01-07 00:00:00.000
7	2011-01-08 00:00:00.000
8	2011-01-09 00:00:00.000
9	2011-01-10 00:00:00.000
10	2011-01-11 00:00:00.000

If you check the table itself, you will see that you have exactly the same result as with the OUTPUT clause

SELECT * FROM TestOutput

id      SomeCol
--      -----------------------
1	2011-01-02 00:00:00.000
2	2011-01-03 00:00:00.000
3	2011-01-04 00:00:00.000
4	2011-01-05 00:00:00.000
5	2011-01-06 00:00:00.000
6	2011-01-07 00:00:00.000
7	2011-01-08 00:00:00.000
8	2011-01-09 00:00:00.000
9	2011-01-10 00:00:00.000
10	2011-01-11 00:00:00.000

Instead of outputting the results right back, you can insert them in a table, temporary table or table variable, I am showing you how to do it with a table variable

DECLARE @TableVar table (id int,SomeDate datetime)

;WITH cte AS(
SELECT TOP 10 ROW_NUMBER() OVER(ORDER BY name) AS number
FROM sysobjects)


INSERT TestOutput
OUTPUT inserted.id,inserted.SomeCol INTO @TableVar
SELECT DATEADD(d,number,'20110101')
FROM cte

SELECT * FROM @TableVar

id      SomeCol
--      -----------------------
11	2011-01-02 00:00:00.000
12	2011-01-03 00:00:00.000
13	2011-01-04 00:00:00.000
14	2011-01-05 00:00:00.000
15	2011-01-06 00:00:00.000
16	2011-01-07 00:00:00.000
17	2011-01-08 00:00:00.000
18	2011-01-09 00:00:00.000
19	2011-01-10 00:00:00.000
20	2011-01-11 00:00:00.000

OUTPUT clause and delete statements

To return the rows that were deleted, you would use deleted.ColumnName instead of inserted.ColumnName.

In the code block below, we are deleting all rows that are 0 if we divide them by 5, this is accomplished by using the modulo arithmetic operator which is % in T-SQL

DELETE TestOutput
OUTPUT deleted.id,deleted.SomeCol
WHERE id % 5 = 0

id      SomeCol
--      -----------------------
5	2011-01-06 00:00:00.000
10	2011-01-11 00:00:00.000
15	2011-01-06 00:00:00.000
20	2011-01-11 00:00:00.000

As you can see we get 4 rows back, if you check what is in the table now, you will see that those 4 rows are missing.

OUTPUT clause and update statements

When you do an update statement, you will have the previous value (deleted) and the current value (inserted) available as part of the OUTPUT clause. Running the code below will output the value you had and the value that it got update to

UPDATE TestOutput
SET SomeCol = dateadd(yy,1,SomeCol)
OUTPUT deleted.id,deleted.SomeCol,inserted.SomeCol
WHERE id in(1,2)

id      SomeCol                 SomeCol
--      ----------------------- -----------------------
1	2011-01-02 00:00:00.000	2012-01-02 00:00:00.000
2	2011-01-03 00:00:00.000	2012-01-03 00:00:00.000

That is all for today, come back tomorrow for another post