You are here

SQL:OUTPUT OPTION

OUTPUT option

OUTPUT OPTION

In this post we will discuss about OUTPUT option. T-SQL supports an OUTPUT clause for modification statements, which we can use to return information from modified rows.

  • We can use output option for purposes like auditing, archiving and others. We can use OUTPUT clause with insert, update, delete statements.
  • This option is similar to select clause, one difference from the select clause is that, in the OUTPUT clause, when you refer to columns from the modified rows we need to prefix the column names with inserted or deleted keyword.

INSERT with OUTPUT:

  • The OUTPUT clause is used with an insert statement to return information from the inserted rows.
  • In an insert statement we cannot use deleted prefix.
  • Here we have use inserted prefix because we insert rows.

Syntax:
Insert into table_name(col1, col2, …..)
OUTPUT
Inserted.colname, inserted.colname, ….
values(exp1, exp2, …..);

DELETE with OUTPUT:

  • The OUTPUT clause is used with delete statement to return information from the deleted rows.
  • Here we have to use the prefix deleted to refers to column that are deleted.

Syntax:
Delete from <table_name>
OUTPUT
deleted.colname, deleted.colname, …
where <predicate>;
UPDATE with OUTPUT:

  • With the use of OUTPUT option in update statement we have access to both the old and new data.
  • In an update statement, inserted prefix represents the state of the rows after the update and deleted represents the state of the rows before the update.

Syntax:
Update <table_name>
Set <col_name>=<exp>,
…….
<col_n>=<exp>
OUTPUT
deleted.col_name as old_data,
inserted,col_name as new_data
where <predicate>;

MERGE with OUTPUT:

  • We can also use OUTPUT option with merge statement in SQL server.
  • Merge statement can perform different action against the target table.so when returning output rows we have to know which action (insert, update or delete) affect the output rows.
  • So SQL server provide the action function to indicate which action is performed.

Syntax:
Merge into <target_tablename> as alias_name
using <source_tablename> as alias_name[or] using(values(exp1,exp2),(exp1,exp2)) as source_tablename(columns)
ON <merge_condition>
when matched then
<update_statement>
when not matched by target then
<insert_statement>
When not matched by source then
<delete_statement>
OUTPUT
$action as alias_name
COALESCE(deleted.col_name, inserted.col_name);

Example:

–Create table
Create table sourcetbl(sn int, ename varchar(50));
–Output with insert
Insert into sourcetbl

output inserted.*

values(1,’manoj’),(2,’hema’),(3,’riya’),(4,’khyati’);
output:

sn

ename
1

manoj

2

hema

3

riya

4

khyati

–Output with delete
Delete from sourcetbl
output deleted.sn, deleted.ename
where sn=4;
output:

sn

ename
4

Khyati

–Output with update
Update sourcetbl
set ename=’pankaj’
output inserted.sn, deleted.ename as old_name, inserted.ename as new_name
where sn=2;
output:

sn

old_name new_name
2 hema

pankaj

Output with Merge:
Merge into sourcetbl as T
using (Values(1,’priya’),(2,’rani’),(3,’kathan’),(4,’Chirag’)) as tar(sn,ename)
on
T.sn=tar.sn
when matched then
update set T.ename=tar.ename
when not matched by target then
insert (sn,ename) values(tar.sn, tar.ename)
output
$action as the_action,
COALESCE(inserted.sn, deleted.sn) as sn;
Output:

the_action

sn

UPDATE

1

UPDATE

2

UPDATE

3

INSERT

4

 

Leave a Reply

Top