Dec 10, 2014

USE of OUTPUT Clause in Sql Server


SQL Server 2005 introducted the OUTPUT clause . We can use the OUTPUT clause with DML statements (INSERT, DELETE, UPDATE) to return information from modified rows.

Following example  may helps you understand OUTPUT clause in sql server.      

--1st step

CREATE TABLE #tmp

(

id INT IDENTITY(1,1),

fName VARCHAR(40),

lName VARCHAR(40)

)

--2nd step

INSERT INTO #tmp (fName,lName)

VALUES('Dilip','Singh')

INSERT INTO #tmp (fName,lName)

VALUES('Anil','Singh')

INSERT INTO #tmp (fName,lName)

VALUES('Param','Tripathi')


--3rd step

INSERT INTO #tmp (fName,lName)

OUTPUT INSERTED.fName,INSERTED.lName

VALUES('Nirmit','Katiyar')

--4th step

DELETE FROM #tmp

OUTPUT DELETED.fName, DELETED.lName

WHERE fName='Anil'

--5th step

UPDATE #tmp  SET fName='Shiva'  OUTPUT INSERTED.fName,INSERTED.lName WHERE fName='Dilip'

--6th step

SELECT * FROM #tmp

--7th step

DROP TABLE #tmp

Result by step respectively

(1 row(s) affected)
(1 row(s) affected)

(1 row(s) affected)

fName                                    lName
---------------------------------------- ----------------------------------------

Nirmit                                   Katiyar
(1 row(s) affected)

fName                                    lName
---------------------------------------- ----------------------------------------

Anil                                     Singh

(1 row(s) affected)

fName                                    lName
---------------------------------------- ----------------------------------------

Shiva                                    Singh


(1 row(s) affected)
id          fName                                    lName

----------- ---------------------------------------- -------------------------------------
1           Shiva                                    Singh

3           Param                                    Tripathi

4           Nirmit                                   Katiyar
(3 row(s) affected)