T-SQL (INSERT)-SQL Server 2008

T-SQL has constantly evolved right from its very beginning and there are lot of new features that have been added to T-SQL over the years. I was working on a project where in I was creating a stored procedure and was writing INSERT sql. There was a need to capture the value inserted into a table and assign that value to a variable. In the INSERT t-sql statement there is a option called OUTPUT, what this clause does is it has access to the inserted table. This is very similar to how inserted table is available in Triggers. The purpose of the inserted table is hold the value that been just Inserted. For example a INSERT Statement with an output clause could look like the following:
INSERT INTO dbo.TestTable
(BatchId,
 ServiceId)
OUTPUT inserted.BatchId,inserted.ServiceId
SELECT BatchId,
ServiceId FROM dbo.LookupTable WHERE ProcessName = @ProcessName.

In the above example the value of BatchId and ServiceId is being routed to inserted table with the columns BatchId and ServiceId. Now just having these in inserted table  would not of much value, these need to be captured. In order to capture the values once can extend the INSERT statement in the following way:

INSERT INTO dbo.TestTable
(BatchId,
ServiceId)
OUTPUT inserted.BatchId,inserted.ServiceId INTO @out(BatchId,ServiceId)
SELECT BatchId,
ServiceId FROM dbo.LookupTable WHERE ProcessName = @ProcessName.

In the above example the values in the inserted table are being routed to a table variable with the 2 columns.
Once the statement is executed one can get the values from the @out table variable.

SELECT BatchId,ServiceId FROM @out.

The above feature gives a elegant way to capture information from a INSERT statement. The above set of queries were executed on SQL Server 2008.