TSQL: OUTPUT Clause

Using OUTPUT clause, it is now possible to return rows affected by data-modification operations - INSERT, UPDATE, DELETE, MERGE.
Rows affected can be returned as a result set or put into an output table and variable.

Samples:

insert into UnitMeasure(UnitMeasureCode, Name)
          output inserted.UnitMeasureCode, inserted.Name, inserted.ModifiedDate
values('apc', 'ato');

----------------------------------------------------

declare @DeletedUnitMeasure table
       (UnitMeasureCode nchar(3),
        Name nvarchar(100))

delete from UnitMeasure
        output deleted.UnitMeasureCode, deleted.Name into @DeletedUnitMeasure
where UnitMeasureCode = 'apc'

select * from @DeletedUnitMeasure

----------------------------------------------------

update UnitMeasure
set Name = 'box'
          output inserted.Name as NewName,
                     deleted.Name as OldName
where UnitMeasureCode = 'BOX'