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

Advertisements

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s