OUTPUT Command

Hi,

Today we will look at OUTPUT command. OUTPUT command is equivalent to a trigger where you can get the old and new values using INSERTED & DELETED tables. This works similarly to trigger but you don't have to create a trigger to get the job done.

The OUTPUT command returns information about rows affected by an INSERT, UPDATE or DELETE command. The result set can be returned to the calling aplication and used for requirements such as archiving or logging / auditing.

In general, the output of INSERT, UPDATE or DELETE is taken into a table variable & then can be used to save it permanently, if required.

Let's look at an example:

Create table OutputSample
(
ID int,
age int,
name varchar(100)
)

--Now lets insert value in the OUTPUTSAMPLE table
Insert into OutputSample values (1,10, 'test')
Insert into OutputSample values (2,15, 'test1')
Insert into OutputSample values (3,20, 'test2')

Select * from OutputSample

--Now let's implement OUTPUT with INSERT statement

--Create a table variable
DECLARE @tabs table (Id int, age int, Name varchar(100))

--Insert into table using OUTPUT - INSERT statement
Insert into OutputSAMPLE
OUTPUT Inserted.ID, Inserted.age, Inserted.Name into @tabs --Putting data into Table variable
values(4,35,'Another One')

Select * from @tabs --check out the values of table variable

select * from OutputSAMPLE --Check out data from Main table

--You can use the same to insert data in a normal table...



--Now let's see an example using UPDATE statement

--Declare a table variable
DECLARE @tabs table
(Id int, age int, Name varchar(100), oldname varchar(100))

--Update the value
Update OutputSAMPLE
Set name='Updated Another One'
OUTPUT Inserted.ID, Inserted.age, Inserted.Name, Deleted.Name into @tabs
Where ID=4

Select * from @tabs --time to check output

Select * from OutputSAMPLE

When the OUTPUT clause is used for an UPDATE command, both DELETED and INSERTED table are available, DELETED table contains the values before the update and the INSERTED table contains the values after the update.




--Now let's see an example using DELETE statement

--Create a table variable
DECLARE @tabs table (Id int, age int, Name varchar(100))


DELETE outputSample
OUTPUT Deleted.ID, Deleted.age, Deleted.Name into @tabs
Where Id=1

Select * from @tabs --check output

Select * from OutputSample

Do let me know if there are any comments.

Enjoy SQL Coding.