OUTPUT Clause In SQL Server

OUTPUT Clause In SQL Server:

OUTPUT clause  returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE statement. We primarily use the OUTPUT clause for auditing and archiving modified rows. We can capture resuls from INSERT, UPDATE, DELETE statements and insert into a table. In this blog, I will explain how to use OUTPUT clause for INSERT,UPDATE and DELETE statements.

First we create an Employee table.

CREATE TABLE [dbo].[Employee](

[Emp_Id] [int] NOT NULL,

[EmployeeName] [nvarchar](max) NULL,

[EmpSalary] [int] NULL,

[StateId] [int] NULL,

[CityId] [int] NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Now insert some data into table.

 

  

OUTPUT Clause with INSERT Statement:

INSERT INTO dbo.Employee

(

Emp_Id,

EmployeeName,

EmpSalary,

StateId,

CityId

)

OUTPUT INSERTED.*

VALUES

(

11, -- Emp_Id - int

N'Nitin', -- EmployeeName - nvarchar

35000, -- EmpSalary - int

1, -- StateId - int

2 -- CityId - int

)

Output:

OUTPUT Clause with Update Statement:

/*Declare Table*/

DECLARE @TAb AS TABLE(

[Emp_Id] [int] NOT NULL,

[EmployeeName] [nvarchar](max) NULL,

[EmpSalary] [int] NULL,

[StateId] [int] NULL,

[CityId] [int] NULL

)

UPDATE dbo.Employee

SET

dbo.Employee.EmployeeName = N'Pankaj Choudhary' -- nvarchar

OUTPUT INSERTED.* INTO @TAb

WHERE dbo.Employee.Emp_Id=1;

SELECT * FROM @TAb t

Output:

 

OUTPUT Clause with DELETE Statement:

DELETE FROM dbo.Employee

OUTPUT DELETED.*

WHERE Emp_Id=2

Output: