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)