OUTPUT Clause in SQL SERVER 2008 - INSERT

In SQL Server 2008, you can add an OUTPUT clause to your data manipulation language (DML) statements. The clause returns a copy of the data that you have inserted into or deleted from your tables. This contains the copy of data you are inserting or updating or deleting.


Here is the example code of OUTPUT clause.


CREATE TABLE [dbo].[SourceTable1](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Price] [money] NULL
)


 Here is the output clause code


 --Decalring temp table
  DECLARE @OUTPUTTABLE Table
  (
ID INT,
NAME VARCHAR(100),
PRICE decimal(18,2)
  );  
  
   INSERT INTO [dbo].[SourceTable]
OUTPUT 
INSERTED.*
INTO @OUTPUTTABLE
  VALUES('Test','25')  
  
  
  SELECT *  FROM [EVSSQL].[dbo].[SourceTable]
  
  SELECT *  FROM @OUTPUTTABLE


You can also define the column names in the query.


 --Decalring temp table
  DECLARE @OUTPUTTABLE Table
  (
ID INT,
NAME VARCHAR(100),
PRICE decimal(18,2)
  ); 
  
  
  INSERT INTO [dbo].[SourceTable]
OUTPUT
INSERTED.Name,
inserted.Price
INTO @OUTPUTTABLE(NAME,Price)
  VALUES('Test','25')  
  
  
  SELECT *  FROM [EVSSQL].[dbo].[SourceTable]
  
  SELECT *  FROM @OUTPUTTABLE