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)

PostgreSQL-Query: Sort result set by specific field values using ORDER BY Clause

Problem: Suppose we have a book_inventory table which has some columns such as id, isbn, title, author, publisher, publish_date, etc.. whe...