Output Clause --Insert Sattement

T-SQL supports the OUTPUT clause after the inception of SQL server 2005 and later editions. We can use the OUTPUT clause with DML statements (INSERT, DELETE, UPDATE) to return information from modified rows.

Both SQL Server 2005 and 2008 let you add an OUTPUT clause to INSERT, UPDATE, or DELETE statements. SQL Server 2008 also lets you add the clause to MERGE statements

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.

We primarily use the OUTPUT clause for auditing and archiving modified rows. In this tutorial, we will walk through the use of the OUTPUT clause with different DML statements and examples. First, we will create a table, dbo.Songs, and populate it with some data.

IF OBJECT_ID('dbo.Songs') IS NOT NULL

DROP TABLE dbo.Songs

GO

CREATE TABLE dbo.Songs

(

Id int CONSTRAINT PK_Songs_Id PRIMARY KEY,

Name varchar(200) NOT NULL,

Singer varchar(50) NOT NULL

)

GO

INSERT INTO dbo.Songs ( Id, Name, Singer)

VALUES (1, 'I hate everything about you', 'Adam Gontier');

INSERT INTO dbo.Songs ( Id, Name, Singer) VALUES (2, 'Dil se', 'A. R. Rahman');

INSERT INTO dbo.Songs ( Id, Name, Singer) VALUES

(3, 'My heart will go On', 'Celine Dion');

INSERT INTO dbo.Songs ( Id, Name, Singer) VALUES (4, 'Maeri', 'Euphoria');

GO

SELECT * from dbo.Songs

GO

1          I hate everything about you     Adam Gontier

2          Dil se                                     A. R. Rahman

3          My heart will go On                 Celine Dion

4          Maeri                                     Euphoria

Inserted and Deleted Tables in an OUTPUT Clause

Inserted and Deleted tables are two memory-resident tables that reside within SQL server and are used with the OUTPUT clause.

Whenever any DML (INSERT, DELETE, UPDATE) statement is executed, these tables are populated.

The results of the INSERT statement are stored in the Inserted table, and the results of the Delete statement are stored in the Deleted table. Also, with an UPDATE statement, the deleted rows are stored in the Deleted table. The new inserted rows in the Inserted table as UPDATE are nothing but delete and insert operations combined together.

Note: You cannot directly query Inserted and Deleted tables to see what data they are currently holding, but you can use them with the OUTPUT clause as well as with Triggers.

The OUTPUT Clause with an Insert statement

When we do an Insert operation on a table, we get a message which reads, “(n row(s) affected),” but if we want to see what data rows were inserted into a table, we can use an OUTPUT clause and a memory resident inserted table to return the results back to the screen in the same way that a select statement does.

Let us insert a record, and use an OUTPUT clause to print the results on the screen.

INSERT INTO dbo.Songs ( Id, Name, Singer)

OUTPUT INSERTED.ID, INSERTED.name, INSERTED.Singer

VALUES (5, 'AINT no grave', 'Johnny Cash');

GO
Check the dbo.Songs table. A new row is inserted with id=5.

select * from dbo.Songs;

GO

ID            name       Singer

5              AINT no grave          Johnny Cash

Id            Name      Singer

1              I hate everything about you      Adam Gontier

2              Dil se       A. R. Rahman

3              My heart will go On  Celine Dion

4              Maeri       Euphoria

5              AINT no grave          Johnny Cash

The OUTPUT Clause with a Delete Statement

The same goes with a delete operation. It shows only (n rows(s) affected). We can use an OUTPUT clause and a deleted table to see which rows were actually deleted from the table.

DELETE from dbo.Songs

OUTPUT DELETED.id, DELETED.name, DELETED.singer

WHERE ID=5;

GO

Query the dbo.Songs table row with id = 5 has been deleted.

select * from dbo.Songs;

GO

id         name    singer

5          AINT no grave   Johnny Cash

Id         Name   Singer

1          I hate everything about you        Adam Gontier

2          Dil se    A. R. Rahman

3          My heart will go On       Celine Dion

4          Maeri    Euphoria

The OUTPUT Clause with an Update Statement

An Update statement does nothing but delete old data and insert new data, so with an Update statement, both memory resident tables are affected and are deleted as well as inserted.

Here we are updating the name of a singer, who has sung ‘Dil se’ song, with ID equal to two.

UPDATE dbo.Songs

SET Singer = 'Rahman'

OUTPUT DELETED.Singer, INSERTED.Singer

WHERE ID = 2;

GO

Singer  Singer

A. R. Rahman     Rahman

You can see the old singer’s name along with the new singer’s name.

select * from dbo.Songs;

Id         Name   Singer

1          I hate everything about you        Adam Gontier

2          Dil se    Rahman

3          My heart will go On       Celine Dion

4          Maeri    Euphoria

The three examples above show how to use an OUTPUT clause for auditing purposes. Now, we will see how to use it for archiving.

Before, we were just printing the results of a DML statement on the screen, which was temporary, but with the OUTPUT clause, you can store the results of a DML statement in a table, too.

Store Results of an OUTPUT Clause into a Table

Inserting the data return from an OUTPUT clause into a table can be done using an OUTPUT INTO clause. Keep in mind that you first need to create the target table which must have the same number of columns and data types that match the source table.

IF OBJECT_ID('dbo.Songs_Inserted') IS NOT NULL

DROP TABLE dbo.Songs_Inserted

GO

CREATE TABLE dbo.Songs_Inserted

(

Id int CONSTRAINT PK_Songs__Inserted_Id PRIMARY KEY,

Name varchar(200) NOT NULL,

Singer varchar(50) NOT NULL

)

GO

INSERT INTO dbo.Songs ( Id, Name, Singer)

OUTPUT Inserted.* INTO dbo.Songs_Inserted

VALUES (5, 'Duniya', 'Piyush Mishra');

GO

-- Result of Songs_Inserted table and base table.

select * from dbo.Songs_Inserted;

select * from dbo.Songs;

GO

Id        Name  Singer

5          Duniya Piyush Mishra

Id        Name  Singer

1          I hate everything about you     Adam Gontier

2          Dil se    Rahman

3          My heart will go On      Celine Dion

4          Maeri    Euphoria

5          Duniya Piyush Mishra

As the results above show, data is inserted into both the tables.

Store Results of an OUTPUT Clause into a Temporary Table

The same goes with a temporary table. Create a temporary table first, and then using an OUTPUT INTO clause, insert the data returned by the OUTPUT clause into a temporary table.

IF OBJECT_ID('tempdb..#Songs_Deleted') IS NOT NULL

DROP TABLE dbo.#Songs_Deleted

GO

CREATE TABLE dbo.#Songs_Deleted

(

Id int,

Name varchar(200) NOT NULL,

Singer varchar(50) NOT NULL

)

GO

DELETE from dbo.Songs

OUTPUT deleted.* INTO dbo.#Songs_Deleted

WHERE ID IN (4,5);

GO

-- Result of temporary table and base table.

SELECT * from dbo.#Songs_Deleted;

Select * from dbo.Songs;

DELETE from dbo.Songs

OUTPUT deleted.Column_name INTO dbo.#Songs_Deleted(Column_Name)

WHERE ID IN (4,5);

GO

Store Results of an OUTPUT Clause into a Table Variable

Nothing changes for table variables as well. Declare a table variable structure the same as a source table. Do not forget to run the entire script at once so that you can see the output inserted into a table variable.

Declare @Songs_Deleted TABLE

(

Id int,

Name varchar(200) NOT NULL,

Singer varchar(50) NOT NULL

)

DELETE from dbo.Songs

OUTPUT deleted.* INTO @Songs_Deleted

WHERE ID IN (1,2);

-- Result of table variable

SELECT * from @Songs_Deleted;

Using an OUTPUT Clause in a 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 second table and populate  IF OBJECT_ID ('Books2', 'U') IS NOT NULL

DROP TABLE dbo.Books2;

 

CREATE TABLE dbo.Books2

(

  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

);

 

-- 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;

For the @MergeOutput1 variable, I include columns for both the old and new titles. I also include a column named ActionType. The column reflects the type of modification that is performed on the table when the rows are merged.

Now let’s look at the OUTPUT subclause. Notice that, in addition to the INSERTED and DELETED column prefixes, I include the $action variable. The built-in parameter returns one of three nvarchar(10) values-INSERT, UPDATE, or DELETE-and is available only to the MERGE statement. The value returned depends on the action performed on the row.

Whether you use want to output your data modifications to a processing application, a table, or a variable, the OUTPUT clause is an easy, effective way to capture that data. By using the clause, you can reduce the number of calls to the database, eliminate the need for auditing triggers, archive modified data, or pass data on to an application for further processing. The OUTPUT clause is a flexible and easy to use-and it offers big benefits. For further information on the clause, see the topic “OUTPUT Clause (Transact-SQL)” in SQL Server Books Online.