By default, the execution of a DML statement such as INSERT, UPDATE, or DELETE does not produce any results that indicate what rows changed except for checking @@ROWCOUNT to determine the number of rows affected.
In SQL Server 2005, the INSERT, UPDATE, and DELETE statements were enhanced to support an OUTPUT clause to be able to identify the actual rows affected by the DML statement. The OUTPUT clause allows you to return data from a modification statement (INSERT, UPDATE, or DELETE).
This data can be returned as a result set to the caller or returned
into a table variable or an output table. To capture information on the
affected rows, the OUTPUT clause provides access to the inserted and deleted virtual tables that are normally accessible only in a trigger. The inserted and deleted
tables provide access to the new/old images of the modified rows; this
is similar to how they provide the information in triggers. In an INSERT statement, you are allowed to access only the inserted table. In a DELETE statement, you are allowed to access only the deleted table. In an UPDATE statement, you are allowed to access both the inserted and deleted tables.
Following is the general syntax of the OUTPUT clause:
UPDATE [ TOP ( expression ) [ PERCENT ] ] tablename
SET { column_name = { expression | DEFAULT | NULL }
| @variable = expression
| @variable = column = expression [ ,...n ]
} [ ,...n ]
OUTPUT
{ DELETED | INSERTED | from_table_name}.{* | column_name} | scalar_expression
[ INTO { @table_variable | output_table } [ ( column_list ) ] ] }
[ FROM { table_name } [ ,...n ] ]
[ WHERE search_conditions ]
DELETE [ TOP ( expression ) [ PERCENT ] ] tablename
OUTPUT { DELETED | from_table_name}.{* | column_name} | scalar_expression
[ INTO { @table_variable | output_table } [ ( column_list ) ] ] }
[ FROM ] table_name
[ FROM table_name [ ,...n ] ]
[ WHERE search_conditions ]
INSERT [ TOP ( expression ) [ PERCENT ] ] [ INTO ] tablename
{
[ ( column_list ) ]
[ OUTPUT { INSERTED | from_table_name}.{* | column_name} | scalar_expression
[ INTO { @table_variable | output_table } [ ( column_list ) ] ] ] }
{ VALUES ( { DEFAULT | NULL | expression } [ ,...n ] )
| SELECT_statement
}
}
The output table (output_table) may be a table variable, permanent table, or temporary table. If column_list is not specified, the output table must have the same number of columns as the OUTPUT result set. If column_list is specified, any omitted columns must either allow NULL
values or have default values assigned to them. Any identity or
computed columns in the output table must be skipped. In addition, output_table
cannot have any enabled triggers defined on it, participate on either
side of a foreign key constraint, or have any check constraints or
enabled rules.
One use of the OUTPUT clause is to verify the rows being deleted, updated, or inserted:
begin tran
delete from sales_big output deleted.*
where sales_id in (select top 10 sales_id from sales_big order by ord_date)
rollback
go
sales_id stor_id ord_num ord_date qty payterms title_id
-------- ------- ------- ----------------------- --- -------- --------
168745 7067 P2121 2005-06-15 00:00:00.000 40 Net 30 TC3218
168746 7067 P2121 2005-06-15 00:00:00.000 20 Net 30 TC4203
168747 7067 P2121 2005-06-15 00:00:00.000 20 Net 30 TC7777
20 7067 P2121 2005-06-15 00:00:00.000 40 Net 30 TC3218
21 7067 P2121 2005-06-15 00:00:00.000 20 Net 30 TC4203
22 7067 P2121 2005-06-15 00:00:00.000 20 Net 30 TC7777
337470 7067 P2121 2005-06-15 00:00:00.000 40 Net 30 TC3218
337471 7067 P2121 2005-06-15 00:00:00.000 20 Net 30 TC4203
337472 7067 P2121 2005-06-15 00:00:00.000 20 Net 30 TC7777
506195 7067 P2121 2005-06-15 00:00:00.000 40 Net 30 TC3218
Another possible use of the OUTPUT clause is as a purge/archive solution. Suppose you want to periodically purge historic data from the sales_big table but also want to copy the purged data into an archive table called sales_big_archive.
Rather than writing a process that has to select the rows to be
archived before deleting them, or putting a delete trigger on the table,
you could use the OUTPUT clause to insert the deleted rows into the archive table.
On approach would be to implement a loop to delete historic data (for example, delete rows for the oldest month in the sales_big table) in chunks, using the TOP clause to specify the chunk size. The OUTPUT clause can be specified to copy the deleted rows into the sales_big_archive table, as shown in Listing 1.
Listing 1. Implementing a Purge/Archive Scenario, Using the OUTPUT Clause
declare @purge_date datetime,
@rowcount int
-- find the oldest month in the sales_big table
select @purge_date = dateadd(day, - (datepart(day, min(ord_date))) + 1,
dateadd(month, 1, min(ord_date))),
@rowcount = 1000
from sales_big
while @rowcount = 1000
begin
delete top (1000) sales_big
output deleted.* into sales_big_archive
where ord_date < @purge_date
set @rowcount = @@rowcount
end
|
In addition to referencing columns in the table being modified by using the INSERTED or DELETED qualifier, you can also retrieve information from another table included in the FROM clause of a DELETE or an UPDATE statement used to specify the rows to update or delete:
begin tran
delete top (5) sales
output t.title_id
from sales s
join titles t on t.title_id = s.title_id
where t.pub_id = '9906'
rollback
go
title_id
--------
FI9620
CH2080
BI7178
CH8924
FI2680
When used with an UPDATE command, OUTPUT produces both a deleted and an inserted table. The deleted table contains the values before the UPDATE command, and the inserted table has the values after the UPDATE command. The OUTPUT clause is also useful for retrieving the value of identity or computed columns after an INSERT or an UPDATE operation. Listing 2 shows an example of OUTPUT being used to capture the computed column as the result of an UPDATE.
Listing 2. Using OUTPUT to Capture a Computed Column
create table UpdateOutputTest
(col1 tinyint,
col2 tinyint,
computed_col3 as convert(float, col2/convert(float, col1)))
go
insert UpdateOutputTest (col1, col2)
output inserted.computed_col3
values (10, 20)
insert UpdateOutputTest (col1, col2)
output inserted.computed_col3
values (10, 25)
go
computed_col3
----------------------
2
computed_col3
----------------------
2.5
declare @output_table TABLE (del_col1 int, ins_col1 int,
del_col2 int, ins_col2 int,
del_computed_col3 float, ins_computed_col3 float,
mod_date datetime)
update UpdateOutputTest
set col2 = col2/5.0
output deleted.col1, inserted.col1,
deleted.col2, inserted.col2,
deleted.computed_col3, inserted.computed_col3,
getdate()
into @output_table
output deleted.computed_col3,
inserted.computed_col3,
getdate() as mod_date
select del_col1, ins_col1, del_col2, ins_col2,
del_computed_col3 as del_col3, ins_computed_col3 as ins_col3,
mod_date from @output_table
go
computed_col3 computed_col3 mod_date
------------- ------------- -----------------------
2 0.4 2010-02-28 19:48:34.240
2.5 0.5 2010-02-28 19:48:34.240
del_col1 ins_col1 del_col2 ins_col2 del_col3 ins_col3 mod_date
-------- -------- -------- -------- -------- -------- -----------------------
10 10 20 4 2 0.4 2010-02-28 19:48:34.240
10 10 25 5 2.5 0.5 2010-02-28 19:48:34.240
|
The UPDATE statement in Listing 2 also demonstrates the capability to use OUTPUT to both insert values into a table and return values to the caller.
Note that the OUTPUT clause is
not supported in DML statements that reference local partitioned views,
distributed partitioned views, remote tables, or INSERT statements that contain an execute_statement. Columns returned from OUTPUT reflect the data as it is after the INSERT, UPDATE, or DELETE statement has completed but before any triggers on the target table are executed.
