OUTPUT Clause

Normally , Modification statement(Insert/Delete/Update) would not return any output except modifying the data. In some scenarios,being able to get data back from the modified rows can be useful. For example ,think advantages of Update statement returning  old and new values of the updated columns. This can be useful in Auditing ,trobleshooting, etc.,

SQL Server supports this capability thru OUTPUT clause

The OUPUT clause will return the requested attributes from the modified rows as result sets like SELECT statement does. IF you want to insert this result into a table add an INTO clause with target table name.

The OUTPUT clause can be used with INSERT,DELETE and UPDATE Statements.

INSERT with OUTPUT:

Following example shows the OUTPUT clause with INSERT statement.

Use AdventureWorks2012

Create table personinfo

(

EntityID Int,

Personname varchar(1000)

)

Insert into personinfo(EntityID,Personname)

OUTPUT inserted.EntityID,inserted.Personname

select BusinessEntityID, CONCAT(FirstName,MiddleName,LastName) as Personname from person.Person

where BusinessEntityID < 100

If you want insert this result set into a table then following query will be helpful

Declare @newrows table(EntityID Int,

Personname varchar(1000)

)

Insert into personinfo(EntityID,Personname)

OUTPUT inserted.EntityID,inserted.Personname

into @newrows

select BusinessEntityID, CONCAT(FirstName,MiddleName,LastName) as Personname from person.Person

where BusinessEntityID > 100 and  BusinessEntityID < 200

select * from @newrows

DELETE with OUTPUT:

Below query shows the OUTPUT clause with DELETE statement.

Delete from personinfo

Output deleted.EntityID,

          deleted.Personname

where EntityID < 100

UPDATE with OUTPUT:

Below query shows the OUTPUT clause with an UPDATE statement.

update personinfo set EntityID += 1--its equal to entityid=entityid+1

output

       inserted.EntityID as newentityid,

       deleted.EntityID as oldentityid

where EntityID < 150

OUTPUT Clause can be used with MERGE statement as well. We will see that MERGE related article.