OUTPUT CLAUSE

Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.
OUTPUT clause can generate table variable, a permanent table, or temporary table.

Using an OUTPUT Clause in an INSERT Statement

When you insert data into a table, you can use the OUTPUT clause to return a copy of the data that’s been inserted into the table. The OUTPUT clause takes two basic forms: OUTPUT and OUTPUT INTO. Use the OUTPUT form if you want to return the data to the calling application. Use the OUTPUT INTO form if you want to return the data to a table or a table variable.

Create Table MyTable (ID int, name varchar(100),createdAt datetime)

-- declare @InsertOutput2 table variable

DECLARE @InsertOutput2 table

(

  ID int,

  name nvarchar(50),

  CreatedAt datetime

);

 -- insert new row into table in your database

INSERT INTO MyTable

OUTPUT

    INSERTED.ID,

    INSERTED.Name,

    INSERTED.CreatedAt

  INTO @InsertOutput2

VALUES(111, 'Some text', GETDATE());

-- view inserted row in table

SELECT * FROM MyTable;

-- view output row in @InsertOutput2 variable

SELECT * FROM @InsertOutput2;

drop table MyTable

Using an OUTPUT Clause in an UPDATE Statement

Create Table MyTable (ID int, name varchar(100),createdAt datetime)

-- declare @InsertOutput2 table variable

DECLARE @UpdateOutput1 table

(

  ID int, 

  name nvarchar(50),

  Oldname nvarchar(50),

  CreatedAt datetime,

  UpdatedAt datetime

);

 -- insert new row into table in your database

INSERT INTO MyTable

VALUES(111, 'Some text', GETDATE());

UPDATE MyTable

SET  

  name = 'Changed...'

OUTPUT   

    INSERTED.ID,

    INSERTED.Name,

    DELETED.Name,

    DELETED.CreatedAt,

    getdate()

  INTO @UpdateOutput1

WHERE Name = 'Some text';

-- view inserted row in table

SELECT * FROM MyTable;

-- view output row in @InsertOutput2 variable

SELECT * FROM @UpdateOutput1;

drop table MyTable

In the same manner you can use it with DELETE statement. It uises the same INSERTED and DELETED memory tables.

Using an OUTPUT Clause in an MERGE Statement

When working in SQL Server 2008, you can add an OUTPUT clause to a MERGE statement. The process is similar to adding the clause to an UPDATE statement; you use both the INSERTED and DELETED column prefixes.

Let’s look at an example that demonstrates how this work. First, however, we must create a second table to support the MERGE statement. The following script creates the Book2 table and populates it with two rows:

CREATE TABLE dbo.Books2

(

  BookID int NOT NULL PRIMARY KEY,

  BookTitle nvarchar(50) NOT NULL,

  ModifiedDate datetime NOT NULL

);

CREATE TABLE dbo.Books

(

  BookID int NOT NULL PRIMARY KEY,

  BookTitle nvarchar(50) NOT NULL,

  ModifiedDate datetime NOT NULL

);

INSERT INTO Books2

     VALUES(101, '100 Years of Solitude', GETDATE());

INSERT INTO Books2

     VALUES(102, 'Pride & Prejudice', GETDATE());

--Once we’ve created the Books2 table, we can try a MERGE statement. In the following example, I declare the @MergeOutput1 variable, merge data from the Books table into the Books2 table, and view the results:

-- declare @MergeOutput1 table variable

DECLARE @MergeOutput1 table

(

  ActionType nvarchar(10),

  BookID int,

  OldBookTitle nvarchar(50),

  NewBookTitle nvarchar(50),

  ModifiedDate datetime

);

INSERT INTO Books

     VALUES(101, 'The Great Gatsby', GETDATE());

-- use MERGE statement to perform update on Book2

MERGE Books2 AS b2

USING Books AS b1

ON (b2.BookID = b1.BookID)

WHEN MATCHED

THEN UPDATE

SET b2.BookTitle = b1.BookTitle

OUTPUT

    $action,

    INSERTED.BookID,

    DELETED.BookTitle,

    INSERTED.BookTitle,

    INSERTED.ModifiedDate

  INTO @MergeOutput1;

-- view Books table

SELECT * FROM Books;

-- view updated rows in Books2 table

SELECT * FROM Books2;

-- view output rows in @MergeOutput1 variable

SELECT * FROM @MergeOutput1;

drop table Books

drop table Books2

$action Gives us the type of action that is performed by Merge statement per record.