Inserted and deleted tables are mainly used inside triggers. But with OUTPUT clause , we can access above virtual tables. That is actually usefull to reduce number of lines when we write quearies. Thanks Aneef for showing me this. I would like to share it with you. Lets try to undestand it using following queary.

/* Create table*/
CREATE TABLE Table1 (AutoID INT IDENTITY(1,1) NOT NULL,SampleText VARCHAR(100))

/*Create temp table*/
DECLARE @TmpTable TABLE (ID INT, SampleText VARCHAR(100),[message] VARCHAR(100))

/*Insert record to Table1 and select inserted records*/
INSERT Table1 (SampleText)
OUTPUT Inserted.AutoID, Inserted.SampleText
VALUES (’Sample value1′)

/*Insert record to Table1 and insert record to temp table with inserted new ID*/
INSERT Table1 (SampleText)
OUTPUT Inserted.AutoID, Inserted.SampleText,’Inserted’ INTO @TmpTable
VALUES (’Sample value2′)

/*Insert record to Table1 and insert record to temp table with inserted new ID*/
INSERT Table1 (SampleText)
OUTPUT Inserted.AutoID, Inserted.SampleText,’Inserted’ INTO @TmpTable
VALUES (’Sample value3′)

/*Select and see data in both tables*/
SELECT * FROM @TmpTable
SELECT * FROM Table1

/*Check how deleted table works*/

UPDATE Table1
SET SampleText = ‘Sample value1′
OUTPUT Deleted.AutoID, Deleted.SampleText,’Deleted record’ INTO @TmpTable
WHERE SampleText=’Sample value1′

UPDATE Table1
SET SampleText = ‘SampleText’
OUTPUT Inserted.AutoID, Inserted.SampleText,’Updated new record’ INTO @TmpTable
WHERE SampleText=’Sample value1′

SELECT * FROM @TmpTable
SELECT * FROM Table1
/*put deleted record to temp table*/

DELETE
FROM Table1
OUTPUT Deleted.AutoID, Deleted.SampleText,’Deleted record’ INTO @TmpTable
WHERE SampleText=’SampleText’

SELECT * FROM @TmpTable
SELECT * FROM Table1

–DROP TABLES
DROP TABLE Table1

Sample SQL (download queary)