Output Clause is introduced in SQL Server 2005 version which is quite useful in various scenarios. It has access to Inserted and Deleted table which is also called the magic tables.
Output Clause is generally used to return the values to the client clause. It can be used with any of the statements like Insert, Update and Delete to find the rows affected by them.
Let's see some examples of it.
Output Clause can generate output in different forms as follows:-
Table Variable
--Creating Student Table
CREATE TABLE Student (StudentID INT, Studentname VARCHAR(100))
GO
--Creating StudentCopy Table to store the output values
CREATE TABLE StudentCopy (StudentID INT, Studentname VARCHAR(100))
GO
--Inserting the output values in Permanent Table
INSERT Student (StudentID, Studentname)
OUTPUT Inserted.StudentID, Inserted.Studentname INTO StudentCopy
VALUES (1,'Manikavelu')
Go
--Display the output
Select * from StudentCopy
Permanent Table
--Creating the Temp Table to store the output values
DECLARE @StudentCopy TABLE(StudentID INT, Studentname VARCHAR(100))
--Inserting the output values in table variable
INSERT Student (StudentID, Studentname)
OUTPUT Inserted.StudentID, Inserted.Studentname INTO @StudentCopy
VALUES (1,'Manikavelu')
--Display the output
Select * from @StudentCopy
Temporary Table
--Creating StudentCopy Table to store the output values
CREATE TABLE #StudentCopy (StudentID INT, Studentname VARCHAR(100))
GO
--Inserting the output values in temp table
INSERT Student (StudentID, Studentname)
OUTPUT Inserted.StudentID, Inserted.Studentname INTO #StudentCopy
VALUES (1,'Manikavelu')
Go
--Display the output
Select * from #StudentCopy
Go
Output Clause can be used with statements like:
Insert Statement
INSERT Student (StudentID, Studentname)
OUTPUT Inserted.StudentID, Inserted.Studentname INTO @StudentCopy
VALUES (1,'Manikavelu')
Update Statement
UPDATE Student
SET Studentname = 'Babu'
OUTPUT Inserted.StudentID, Inserted.Studentname, Deleted.StudentID, Deleted.Studentname INTO TableName
WHERE StudentID = 1
Delete Statement
DELETE
FROM Student
OUTPUT Deleted.StudentID, Deleted.Studentname INTO TableName
WHERE StudentID = 1
Hope the preceding examples have shown you the proper usage of the Output Clause in SQL SERVER.