Tags


I really appreciate and use in my day-to-day life one very useful feature of SQL Server 2005: OUTPUT Clause. It is quite useful to say the least.

OUTPUT clause allows you to retrieve data affected by the INSERT, UPDATE and DELETE statements easily by accessing columns from the INSERTED and the DELETED tables (virtual or magic tables) just like triggers.

The result from the OUTPUT clause can also be inserted into a table or table variable.

Lets understand OUTPUT clause with some examples that we use in out day-to-day life:

——————————————————————————————–

— Example 1: OUTPUT clause into table with INSERT

——————————————————————————————-

— Changing the Database to AdventureWorks

USE AdventureWorks

— Creating table to store records from Department table

CREATE TABLE [dbo].Department_Snapshot

(

Dept_ID INT,

Name_of_Dept VARCHAR(50),

Group_Name VARCHAR(50)

)

— Creating the temp table to store the values from OUTPUT clause

DECLARE @temp_table TABLE

(

Dept_ID INT,

Name_of_Dept VARCHAR(50),

Group_Name VARCHAR(50)

)

— Inserting records in the permanent table as well as the temp table from the OUTPUT clause

INSERT INTO [dbo].Department_Snapshot

OUTPUT Inserted.Dept_ID, Inserted.Name_of_Dept, Inserted.Group_Name INTO @temp_table

SELECT DepartmentID, [Name], GroupName FROM HumanResources.Department

— Checking the values in both the tables, values in both the table should be same

SELECT * FROM Department_Snapshot

SELECT * FROM @temp_table

— Drop the created table

DROP TABLE Department_Snapshot

——————————————————————————————–

— Example 1: OUTPUT clause into table with UPDATE

——————————————————————————————-

I am going to show the example which can be modified to use with DELETE as well as INSERT command.

— Change the Database

USE AdventureWorks

— Declaring the Temp table to show the changes

DECLARE @temp TABLE

(

        DepartmentID_New INT,

        Name_New VARCHAR(100),

        GroupName_New VARCHAR(100),

        DepartmentID_Old INT,

        Name_Old VARCHAR(100),

        GroupName_Old VARCHAR(100)

)

— Selecting the values from HumanResources.Department table to see the current data prior making changes

SELECT * FROM HumanResources.Department

WHERE GroupName=‘Research and Development’


— Updating the values and inserting the changes in the @temp table. Observe carefully. It’s getting interesting..

UPDATE  HumanResources.Department

SET GroupName=‘Research and Development MODIFIED’

OUTPUT Inserted.DepartmentID, Inserted.[Name], Inserted.GroupName, Deleted.DepartmentID, Deleted.[Name], Deleted.GroupName INTO @temp

WHERE GroupName=‘Research and Development’

SELECT * FROM @temp

SELECT * FROM HumanResources.Department

WHERE GroupName=‘Research and Development MODIFIED’

OUTPUT clause is a very powerful and useful feature that enables us to eliminate the use of triggers in some cases and helps to make our code less complicated. Hope you enjoyed this.

Thanks and Cheers,

Subhro Saha

Advertisements