SQL Server:Output Clause

Couple of days back, one of my colleague came to me asking for help. He is inserting multiple record from a XML to a table which has identity column . He need those newly generated identity values to insert into one more supporting table. This is a very common scenario and it can be achieved by using the OUTPUT clause available in SQL Server 2005 onward.

Till SQL server 2005, the logical/magical tables Updated and Deleted can be accessed only through the triggers.From SQL server 2005 onward it can be accessed as part of INSERT,UPDATE and DELETE statement using the OUTPUT clause. Let us discuss the usage of output clause in this post.

Updated and Deleted are two logical/magical table exists in the SQL server as part of the DML operation.While inserting a new record into a table , the same record along with identity value and default values will be available in the Updated table. During the update operation, the Updated table holds the new data and Deleted table holds a old copy of records. While deleting the records,Deleted table holds a copy of the deleted records. It will be more clear by looking into the below example.

USE MYDB
GO
CREATE TABLE Employee
(

   EMP_Id          INT IDENTITY(1,1)    NOT NULL,
   Emp_Name        VARCHAR(100)        NOT NULL,
   Emp_LastName    VARCHAR(100)        ,
   Emp_DOB         DATE,
   emp_DOJ         DATETIME            DEFAULT GETDATE()
)
GO
INSERT INTO Employee(Emp_Name,Emp_LastName,Emp_DOB) OUTPUT inserted.*VALUES ('William','George','1986-04-12')
GO
UPDATE   Employee SET Emp_LastName='John'  OUTPUT deleted.*,inserted.*WHERE Emp_id=1
GO
DELETE FROM Employee  OUTPUT deleted.*  WHERE Emp_id=1


The output of the above statements shows capturing the values of identity column/ column which has default values is much easier using the OUTPUT clause. The output of the OUTPUT clause can be put it in a table or a table variable. Let us see a sample below:

--Inserting the output of output clause into TableVariable
DECLARE @Employee TABLE (Emp_id INT,Emp_name VARCHAR(100),Emp_DOJ DATETIME)



INSERT INTO Employee(Emp_Name,Emp_LastName,Emp_DOF)
OUTPUT inserted.emp_id,inserted.emp_name,inserted.emp_DOJ INTO @Employee VALUES ('William','George','1986-04-12')



SELECT * FROM @Employee GO
--Inserting the output of output clause into Table for maintainging the history
CREATE TABLE Employee_History
(
   History_id          INT IDENTITY(1,1)   NOT NULL PRIMARY KEY,
   EMP_Id              INT                 NOT NULL ,
   Emp_Name            VARCHAR(100)        NOT NULL,
   Emp_LastName        VARCHAR(100)        ,
   Emp_DOB             DATE                ,
   emp_DOJ             DATETIME            ,
   InsertdDate         DATETIME            )GO
UPDATE   Employee
SET Emp_LastName='John' 
OUTPUT deleted.*,GETDATE() INTO  Employee_History(emp_id,Emp_Name,Emp_LastName,Emp_DOB,Emp_DOJ,InsertdDate)WHERE Emp_id=1





If you liked this post, do like my page on FaceBook