SQL Server OUTPUT Clause

In SQL SERVER an OUTPUT keyword/ Clause is a very good feature for auditing purpose.

It performs with Data manipulation language (DML) statements and returns a copy of the data that you have inserted, updated or deleted from table.

You can also store the return data by OUTPUT clause into temp table or permanent table.

Types of OUTPUT Clause:

1-OUTPUT : It returns the copy of data as in result set.

Syntax:
INSERT INTO tblname OUTPUT INSERTED.Column 1, INSERTED.Column 2.... INSERTED.Columns n
 VALUES(Value_1, Value_2, Value n)

2-OUTPUT INTO : It is used, when you want to copy data into table or a table variable.

Syntax:
INSERT INTO tblname OUTPUT INSERTED.Column 1, INSERTED.Column 2.... INSERTED.Columns n 
INTO #temptablename
VALUES(Value_1, Value_2, Value n)

OUTPUT Clause support two types of columns prefix:

• INSERTED: Is a column prefix that specifies the value added by the insert or update statement.
• DELETED: Is a column prefix that specifies the value deleted by the update or delete statement.

Let’s start with an example:

Step 1: Create table and insert few records

create table employee
(
id int IDENTITY(1, 1) primary key,
firstname varchar(10) not null,
lastname varchar(10) not null,
country varchar(10) not null
)

insert into employee(firstname, lastname,Country) 
values('King','Lee','China')

 Step 2: Now insert record with OUTPUT clause

insert into employee
OUTPUT inserted.firstname, 
inserted.lastname,
inserted.Country 
values('Lui','OOi','Japan')

When you insert record into table using OUTPUT clause with column prefix INSERTED, it returns the inserted data as in result set.

Step 3: Update records with OUTPUT clause

Here we using Deleted column prefix before firstname & lastname, because we want to add old deleted changes into table output and Inserted prefix before country name means there is no change in country name.

Update employee

Set firstname='Binoy', lastname='Ui' OUTPUT

deleted.firstname,deleted.lastname, inserted.Country

where id=1

Step 4: Delete records with OUTPUT Clause

Delete employee OUTPUT deleted.id,
deleted.firstname,
deleted.lastname, 
deleted.Country
where id=1

How to Insert Deleted rows into Temp table using OUTPUT Clause?

Step 1: Create one temp table

Create table #employee 
(id int IDENTITY(1, 1) primary key,
firstname varchar(100),
lastname varchar(100),
Country varchar(100))

Step 2: Now delete particular record from employee table, here we used OUTPUT clause with INTO.

Delete employee OUTPUT deleted.id,
deleted.firstname,
deleted.lastname, 
deleted.Country
INTO #employee 
where id=2

Hope you enjoyed the post, Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.




Recommended SQL Server Post:

Dirty Read Concurrency Problems

SQL CAST() Vs TRY_CAST() function

REPLICATE() function

SQL Server Configuration Functions

Query Optimization Technique

SQL Commands

Identity functions in SQL Server

Alter table statement

SQL Keys