(SQL Server )Output Clause $action Function

In SQL Server 2005 and above there is a feature of displaying an output result by output clause.The clause returns a copy of the data which user has inserted or deleted.

User can return the data into a table variable, temporary or parmanent table or to the processing application that's call the DML statements. We can use this as a archieving,confirmation message or other application requirements.

SQL 2005 allows user to add on output clause to insert,update and delete statement.Sql 2008 make us eligible to use output clause with Merge statement also.

In this blog we will learn how to use output clause with these statements.

Using with Insert Statement
*****************************************************
--Created Main Table which is actual table
Create Table TEST_INSERTED
(
EMP_NAME VARCHAR(100)
)
--Created Recorded Table where data needs to be inserted 
Create Table TEST_INSERTED_RECORDED_Example1
(
EMP_NAME VARCHAR(100)
)
--Inserted record into Actual Table
Insert into TEST_INSERTED
Output
INSERTED.*
INTO TEST_INSERTED_RECORDED_Example1
values('ABC')

--Get Record into Actual Table
 Select * from TEST_INSERTED

--Get Record into Recorded Table
Select * from TEST_INSERTED_RECORDED_Example1

User can see same record in both the table
******************************************************
Using with Update Statement
********************************************************We will use same actual table for update statement but we will create new recorded table, as we will store old value and new value both.

--Created Recorded Table where data needs to be inserted
Create Table TEST_INSERTED_RECORDED_Example2
(
NEW_EMP_NAME VARCHAR(100),
OLD_EMP_NAME VARCHAR(100)
)

--Write Update Statement
Update TEST_INSERTED
SET EMP_NAME='ABC_NEW_NAME'
output
Deleted.Emp_Name,
Inserted.Emp_Name
into
TEST_INSERTED_RECORDED_Example2
WHERE EMP_NAME='ABC'

--Get Record into Actual Table
Select * from TEST_INSERTED

--Get Record into Recorded Table
Select * from TEST_INSERTED_RECORDED_Example2

******************************************************
Using with Delete Statement
********************************************************We will use same actual table for delete statement but we will create new deleted table

--Created Recorded Table where data needs to be inserted 
Create Table TEST_INSERTED_RECORDED_Example3
(
Deleted_EMP_NAME VARCHAR(100)
)

--Write Update Statement
Delete from TEST_INSERTED
Output Deleted.* Into TEST_INSERTED_RECORDED_Example3


--Get Record into Actual Table
Select * from TEST_INSERTED

--Get Record into Recorded Table
Select * from TEST_INSERTED_RECORDED_Example3

******************************************************

Happy Reading.......
Sanjay
mesanjaybakshi@gmail.com