Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Vivek's SQL Notes

Vivek johari is currently a Analyst and have more that 5.5 yeras of experience in database. He has Master degree in Computer and also he is Microsoft certified Sql DBA (MCTS)& Microsoft certified SQl BI professional(MCTS). He is also Oracle certified profession(OCP)DBA in ORACLE 10g and ORACLE 9i.He has the experience of working in PL/SQL, T-SQL and SSIS/SSRS. His work basically involved designing and optimization of the Database.He has also published many database articles on his blog Technologies with Vivek Johari.

SQL Server- Output clause

Output Clause:- The SQL SERVER 2005 gives us an Output Clause which gives us the information about each row affected by the Insert, Update , Delete and Merge statement. It is more useful than @@scope_Identity and @@Identity column since these global variables gives us the information about the last inserted identity column value, but the Output clause gives us the last affected identity in case of Inserted, deleted, updated and merge statement. Output clause has the access to the Magic tables (Inserted and deleted) and we can capture the records of affected rows into the temporary tables too for latter use.


Create table Tbl_output (id int identity(1,1), fname nvarchar(100), lname nvarchar(100))


Example of Output clause in case of Insert command



Declare @outputtemp Table  (id int , fname nvarchar(100), lname nvarchar(100))

Insert into tbl_output(fname,lname)
Output inserted.id, inserted.fname, inserted.lname into @outputtemp
values('Vivek', 'Johari')


Select * from @outputtemp






Example of Output clause in case of Update command


Update tbl_output set fname='Vibodh' 
Output inserted. fname , deleted.fname
where id=1


Example of Output clause in case of Delete command


Delete from tbl_output 
Output deleted.* 
where id=1


Read more about Output Clause

Comments

Leave a comment on the original post [feedproxy.google.com, opens in a new window]

Loading comments...