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 TableCREATE TABLE Student (StudentID INT, Studentname VARCHAR(100))GO--Creating StudentCopy Table to store the output valuesCREATE TABLE StudentCopy (StudentID INT, Studentname VARCHAR(100))GO--Inserting the output values in Permanent TableINSERT Student (StudentID, Studentname)OUTPUT Inserted.StudentID, Inserted.Studentname INTO StudentCopyVALUES (1,'Manikavelu')Go--Display the outputSelect * from StudentCopyPermanent Table--Creating the Temp Table to store the output valuesDECLARE @StudentCopy TABLE(StudentID INT, Studentname VARCHAR(100))--Inserting the output values in table variableINSERT Student (StudentID, Studentname)OUTPUT Inserted.StudentID, Inserted.Studentname INTO @StudentCopyVALUES (1,'Manikavelu')--Display the outputSelect * from @StudentCopyTemporary Table--Creating StudentCopy Table to store the output valuesCREATE TABLE #StudentCopy (StudentID INT, Studentname VARCHAR(100))GO--Inserting the output values in temp tableINSERT Student (StudentID, Studentname)OUTPUT Inserted.StudentID, Inserted.Studentname INTO #StudentCopyVALUES (1,'Manikavelu')Go--Display the outputSelect * from #StudentCopyGoOutput Clause can be used with statements like:Insert StatementINSERT Student (StudentID, Studentname)OUTPUT Inserted.StudentID, Inserted.Studentname INTO @StudentCopyVALUES (1,'Manikavelu')Update StatementUPDATE StudentSET Studentname = 'Babu'OUTPUT Inserted.StudentID, Inserted.Studentname, Deleted.StudentID, Deleted.Studentname INTO TableNameWHERE StudentID = 1Delete StatementDELETEFROM StudentOUTPUT Deleted.StudentID, Deleted.Studentname INTO TableNameWHERE StudentID = 1Hope the preceding examples have shown you the proper usage of the Output Clause in SQL SERVER.
You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.
Become a sharper developer and jumpstart your career.
$0
$
. 00
monthly
For Basic members:
$20
For Premium members:
$45
For Elite members: