SQL Server 2005 introduced the OUTPUT clause in DML statements. With the OUTPUT clause, you can output column values to a table variable, a table, or return these values to the user. You can even use multiple OUTPUT clauses to push the values into both a table variable and a table from the same statement.
In SQL Server 2008 there is an additional option. You can use your OUTPUT column values directly in an INSERT-SELECT statement. Here's what it would look like, using MERGE with an OUTPUT clause (and an example from one of the early webcasts):
CREATE TABLE Stock (Stock VARCHAR(10) PRIMARY KEY, Qty INT CHECK (Qty > 0));
CREATE TABLE Trades (Stock VARCHAR(10) PRIMARY KEY, Delta INT CHECK (Delta <> 0));
CREATE TABLE AuditChanges (Action varchar(6), Stock VARCHAR(6), Qty INT);
GO
INSERT Stock VALUES('MSFT', 10), ('BOEING', 5);
INSERT Trades VALUES('MSFT', 5), ('BOEING', -5), ('GE', 3);
GO
INSERT INTO AuditChanges
SELECT * FROM
(
MERGE Stock S
USING Trades T
ON S.Stock = T.Stock
WHEN MATCHED AND (Qty + T.Delta = 0) THEN
DELETE
WHEN MATCHED THEN
UPDATE SET Qty += T.Delta
WHEN NOT MATCHED THEN
INSERT VALUES(Stock, T.Delta)
OUTPUT $action, T.Stock, inserted.Qty
) tab (action, stock, qty);
GO
Notice how the OUTPUT clause requires a table alias (in this case "tab") and needs to name the columns returned from OUTPUT.