The OUTPUT clause is a useful new statement that can be used to return records that were inserted or deleted after an insert or delete respectively. This can be useful if you want to return the row that was just inserted from a stored procedure.
In the example below, a temporary table is created. The schema of this temporary table would match that of the table that you are inserted into. The OUTPUT clause on the INSERT statement is the used to directed the inserted values into the temporary table. Lastly, a select is performed on the temporary table.
CREATE PROC MyInsert @Description varchar(64)
AS
/* This would be a temporary table */
DECLARE @TempTable TABLE (RecordId in, Description varchar(64))
/* Insert a new item and output into a temp table */
INSERT INTO MyTable (Description)
OUTPUT inserted.* INTO @TempTable
VALUES(@Description)
/* return the values of the temporary table */
SELECT * From @TempTable
Read the complete post at http://www.dotnettipoftheday.com/blog.aspx?id=178