SQL Server 2005 Output Clause

The OUTPUT clause helps in returning the actual data from a table soon after an insert, update or delete. Output clause uses two virtual tables inserted and deleted. Output clause can be useful to detect actual rows affected by DML statements.


CREATE TABLE Emp(
EmpName VARCHAR(9),Age INT,MaritalStatus char(1))

INSERT INTO Emp VALUES ('Dhvani',20,'M')
INSERT INTO Emp VALUES ('Nehal',20,'M')
INSERT INTO Emp VALUES ('Sunil',95,'M')
INSERT INTO Emp VALUES ('Suvrendu',40,'M')
INSERT INTO Emp VALUES ('Bill',11,'S')
INSERT INTO Emp VALUES ('Ram',100,'S')
INSERT INTO Emp VALUES ('Nirmal',50,'S')
INSERT INTO Emp VALUES ('R',30,'S')

Declare @dummyEMp as table (EmpName VARCHAR(9),Age INT,MaritalStatus char(1))

INSERT INTO Emp (EmpName,Age,MaritalStatus)
OUTPUT inserted.EmpName,inserted.Age,inserted.MaritalStatus INTO @dummyEMP
VALUES ('Dhvani',20,'M')

select * from @dummyEMp

Declare @dummyEMp as table (EmpName VARCHAR(9),Age INT,MaritalStatus char(1))
INSERT INTO Emp (EmpName,Age,MaritalStatus)
OUTPUT inserted.* INTO @dummyEMP
VALUES ('Deepali',27,'M')
select * from @dummyEMp


Declare @dummyEMp as table (EmpName VARCHAR(9),Age INT,MaritalStatus char(1))
update Emp
set MaritalStatus = 'M'
output deleted.* into @dummyEMP
where EmpName = 'Dhvani'
select * from @dummyEMp

Declare @dummyEMp as table (EmpName VARCHAR(9),Age INT,MaritalStatus char(1))
delete from Emp
output deleted.* into @dummyEMP
where Age > 50
select * from @dummyEMp