Output Clause in SQL Server

We can use the OUTPUT clause with DML statements (INSERT, DELETE, and UPDATE) to return information from modified rows. This clause is most commonly used for audit purpose. By using this someone can implement a custom process that would work like CDC, or act in the same way as CDC. In addition, results from the OUTPUT clause can be returned to the processing applications for use in such things as confirmation messages, logging and any other application requirements.

OUTPUT gives access to two virtual tables (Magic Tables). These are:

Ø  “INSERTED” contains the new rows (INSERT or UPDATE‘s SET)

Ø  “DELETED” contains the old copy of the rows(empty for INSERT)

Read:  INSERTED and DELETED Logical Tables in SqlServer

http://bageshkumarbagi-msbi.blogspot.in/2017/08/inserted-and-deleted-logical-tables-in.html

See the example

I am creating two tables

CREATE TABLE Books (   BookID int NOT NULL PRIMARY KEY,   BookTitle nvarchar(50) NOT NULL ); CREATE TABLE Books_Output (   BookID int NOT NULL PRIMARY KEY,   BookTitle nvarchar(50) NOT NULL );

Table is created successfully.

Now I am inserting record a record in table Books and using Books_Output table using OUTPUT parameter.

INSERT INTO Books OUTPUT INSERTED.*   INTO Books_Output VALUES(101, 'C#')

See the output.

See the value in both tables.

  

When we insert data into a table, we 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 we want to return the data to the calling application. Use the OUTPUT INTO form if we want to return the data to a table or a table variable.

Using an OUTPUT Clause in an UPDATE Statement

The OUTPUT clause includes the INSERTED column prefix in the OUTPUT sub clause. However, the OUTPUT clause supports a second column prefix-DELETED. The DELETED prefix returns the values that have been deleted from a table. This is important because an UPDATE operation is actually two operations-a deletion and an insertion. As a result, we use both the INSERTED and DELETED column prefixes when adding an OUTPUT clause to an UPDATE statement.

See the example

I am creating a new table which store the old and new value

create table Books_Audit(   OldBookID int,   NewBookID int,   OldBookTitle nvarchar(50),   NewBookTitle nvarchar(50) );

Table created successfully.

Updating the record

UPDATE Books SET   BookID = 110,   BookTitle = 'Update C#' OUTPUT     DELETED.BookID,     INSERTED.BookID,     DELETED.BookTitle,     INSERTED.BookTitle   INTO Books_Audit WHERE  BookID = 101;

See the output.

Using an OUTPUT Clause in a DELETE Statement

I am creating a table which stores the deleted records.

create table Books_Deleted_Records (   BookID int,   BookTitle nvarchar(50) );

Table is created successfully. Now I am deleting the record from Books table.

DELETE Books OUTPUT DELETED.*   INTO Books_Deleted_Records WHERE BookID = 102;

See the result in table.

OUTPUT Clause Limitations

Some of the scenarios where the OUTPUT clause is not supported:

Ø  DML statements that reference local partitioned views, distributed partitioned views, or remote tables.

Ø  INSERT statements that contain an EXECUTE statement.

Ø  Full-text predicates are not allowed in the OUTPUT clause when the database compatibility level is set to 100.

Ø  The OUTPUT INTO clause cannot be used to insert into a view, or row set function.

Ø  A user-defined function cannot be created if it contains an OUTPUT INTO clause that has a table as its target