SQL 2005 - OUTPUT clause with DML

SQL Server 2005 now introduces an OUTPUT clause as a part of DML statements that can help you in tracking changes made during any DML operation. The OUTPUT clause can save the resultset in a table or table variable. This functionality is similar to what triggers had with INSERTED and DELETED tables which used to access the rows that have been modified during the DML operation.

Example: Let's change the address from the address table to the reverse of the original value.
--Create the address table
Create Table Address (ProductID Int, SupplierID Int, Address Varchar(255))
--Insert data
Insert into Address Values (234,567,'1234 One SQL Way, Microsoft City, U.S.')
Insert into Address Values (345,678,'1234 One Windows Way, Microsoft City, WA')
--Declare a table variable
Declare @Recordchanges table (change Varchar(255))
--Update the address
Update Supplier.Address Set Address=reverse(address)
--Record the updates into the table variable
OUTPUT 'Original Value:' + DELETED.Address+' has been changed to: '+ INSERTED.Address+'' into @RecordChanges
--Query the changes from table variable
Select * from @RecordChanges

--Result-set
------------------------
Original Value:'1234 One SQL Way, City, U.S.' has been changed to: '.S.U ,ytiC,yaW LQS enO 4321'
Original Value:'1234 One Windows Way, City, WA' has been changed to: 'AW ,ytiC ,yaW swodniW enO 4321'