TSQL INSERT, UPDATE, DELETE with the OUTPUT clause

A new feature with SQL Server 2005 is the ability to add an OUTPUT to an INSERT, UPDATE or DELETE statement.

The OUTPUT clause allows you to retrieve data affected by the INSERT, UPDATE, or DELETE statement.

The resulting output can be selected, inserted into a table, or inserted into a table variable, making it ideal for all sorts of uses. The OUTPUT Clause (Transact-SQL) article in SQL Server 2005 Books Online (September 2007) defines it as useful for "such things as confirmation messages, archiving, and other such application requirements. "

For an INSERT, you can specify any of the values being included in the INSERT statement, such as in this example from SQL Server 2005 Books Online (September 2007):

USE AdventureWorks;
GO
DECLARE @MyTableVar table( ScrapReasonID smallint,
Name varchar(50),
ModifiedDate datetime);
INSERT Production.ScrapReason
OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

For an UPDATE you can include INSERTED or DELETED and values from_table_name (more explained below), and for DELETE, you can include DELETED values and values from_table_name.

from_table_name refers to a table used in the FROM clause in determining the rows for UPDATE, DELETE.

A delete example from SQL Server 2005 Books Online (September 2007) (note the reference to Production.Product table in the OUTPUT clause):

USE AdventureWorks
GO
DECLARE @MyTableVar table (
ProductID int NOT NULL,
ProductName nvarchar(50)NOT NULL,
ProductModelID int NOT NULL,
PhotoID int NOT NULL);

DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
p.Name,
p.ProductModelID,
DELETED.ProductPhotoID
INTO @MyTableVar
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p
ON ph.ProductID = p.ProductID
WHERE p.ProductModelID BETWEEN 120 and 130;

This forum post explains the reason why values from_table_name cannot be used for an INSERT statement, and the explanation makes sense:
"The UPDATE and DELETE however has the non-standard TSQL specific FROM clause as part of the DML statement itself so you can reference those tables in the OUTPUT clause."

This article also offers some additional information on the subject: http://www.dbnewsgroups.net/group/microsoft.public.sqlserver.programming/topic12325.aspx