USE of the OUTPUT Clause in Sql Server



SQL Server 2005 introduced 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)

Related Posts

Fatal Error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails.

Problem: Sometimes, we get the following error when trying to make a connection in Rails.  FATAL ERROR: Peer authentication failed for us...