OUTPUT clause and MERGE statement

Accroding to MS-BOL, OUTPUT clause returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application for use in such things as confirmation messages, archiving, and other such application requirements. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

Lets go with a self descriptive example:

–// OUTPUT with INSERT

1	create table manoj (sn int, ename varchar(50))

2	insert into manoj

3	OUTPUT INSERTED.*

4	values (1,'manoj'), (2,'hema'), (3,'kanchan'), (4,'pankaj')

This gives me following output instead of normal message (N row(s) affected):
sn  ename
1 manoj
2 hema
3 kanchan
4 pankaj

1	select * from manoj

This gives me the same output as above:
sn  ename
1 manoj
2 hema
3 kanchan
4 pankaj

–// OUTPUT with DELETE

1	delete from manoj

2	OUTPUT DELETED.*

3	where sn = 4

This gives me following output:
sn  ename
4 pankaj

1	select * from manoj

Now the result set is changed to:
sn  ename
1 manoj
2 hema
3 kanchan

–// OUTPUT with UPDATE

1	update manoj

2	set ename = 'pankaj'

3	OUTPUT DELETED.*, INSERTED.*

4	from manoj

5	where sn = 2

This gives me following output:
sn  ename  sn   ename
2 hema 2 pankaj

1	select * from manoj

Now the result set is changed to:
sn  ename
1 manoj
2 pankaj
3 kanchan

–// OUTPUT with MERGE

According to MS-BOL, MERGE performs insert, update, or delete operations on a target table based on the results of a join with a source table. For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

1	create table manoj2 (sn int, ename varchar(50))

2

3	insert into manoj2

4	values (1,'manoj'), (2,'hema'), (3,'kanchan'), (4,'pankaj'), (5,'saurabh')

5

6	select * from manoj2

This gives me following output instead of normal message (N row(s) affected):
sn  ename
1 manoj
2 hema
3 kanchan
4 pankaj

1	MERGE manoj AS TARGET

2	USING (SELECT sn, ename FROM manoj2) AS SOURCE

3	ON (TARGET.sn = SOURCE.sn)

4	WHEN MATCHED THEN

5	UPDATE SET TARGET.ename = SOURCE.ename

6	WHEN NOT MATCHED BY TARGET THEN

7	INSERT (sn, ename) VALUES (sn, ename)

8	OUTPUT $action, DELETED.*, INSERTED.*;

This gives me following output:
$action sn  ename  sn  ename
INSERT NULL NULL 4 pankaj
INSERT NULL NULL 5 saurabh
UPDATE 1 manoj 1 manoj
UPDATE 2 pankaj 2 hema
UPDATE 3 kanchan 3 kanchan

1	select * from manoj

Now the result set is changed to:
sn  ename
1 manoj
2 hema
3 kanchan
4 pankaj
5 saurabh

–// Final cleanup

1	drop table manoj

2	drop table manoj2

Plz note: An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement.

MS BOL:-

- On MERGE: http://technet.microsoft.com/en-us/library/bb510625.aspx

- On OUTPUT: http://technet.microsoft.com/en-us/library/ms177564.aspx