SQLserver 2005: OUTPUT Clause
What: OUTPUT Clause is used to return the rows affected by Insert, Update or Delete Statement on SQL table
Why: To perform an action on the rows which are affected by any of the table manipulation statements
i.e. Insert, Update and Delete.
Example: suppose, we have a requirement to maintain a log file after any operation on a table. Here operation refers to Insert, Update and Delete operation.
The role of an Output Clause comes here, Instead of using a trigger we could use the OUTPUT clause.
How: There are two tables here:
- tblExternalFile: a parents Table
- tblExternalFileBackup : a Backup Table
Structures of these 2 tables are bellow:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblExternalFile]( [Id] [int] IDENTITY(1,1) NOT NULL, [FileXml] [xml] NULL, CONSTRAINT [PK_tblExternalFile] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY] ) ON [PRIMARY]
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[tblExternalFileBackup]( [Id] [int] IDENTITY(1,1) NOT NULL, [FileXml] [xml] NULL, CONSTRAINT [PK_tblExternalFileBackup] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
Here both tables have same numbers of column,
OUTPUT clause with Insert:
Here is a code that will automatically insert a new record in – tblExternalFileBackup
table,whenever an insert operation fires on – tblExternalFile table.
GO -- Create Temp Table variable DECLARE @MyTableVar table( Id int, FileXml xml, ModifiedDate smallDateTime); -- Insert Operation Fires on tblExternalFile INSERT tblExternalFile([FileXml]) OUTPUT INSERTED.Id,INSERTED.FileXml,GetDate() INTO @MyTableVar VALUES (N'<DatabaseData xmlns=""><MappingMeta>123</MappingMeta></DatabaseData>'); -- Insert a newly Added Record to tblExternalFileBackup INSERT INTO tblExternalFileBackup(FileXml) SELECT FileXml FROM @MyTableVar --Display the result set of the table variable. SELECT * FROM tblExternalFileBackup; GO OUTPUT clause with Delete: DELETE FROM tblExternalFile OUTPUT DELETED.* WHERE ID=17 All Deleted rows will be displayed whose Id is 17.
OUTPUT clause with Update:
-- Create Temp Table variable DECLARE @MyTableVar table( Id int, OldFileXml xml, NewFileXml xml, ModifiedDate smallDateTime);
UPDATE tblExternalFile SET FileXml = '<Employee xmlns=""><ID>007</ID></Employee>' OUTPUT INSERTED.Id, DELETED.FileXml, INSERTED.FileXml, GETDATE() INTO @MyTableVar WHERE iD=17;
— Insert a newly Added/ Updated Record to tblExternalFileBackup
INSERT INTO tblExternalFileBackup(FileXml)
SELECT FileXml FROM @MyTableVar
SELECT * FROM tblExternalFileBackup
@MyTableVar is a table Variable we use it to store the Updated records of a Table.
Here an Id Number ‘17’ will be updated with the New Xml String and new row will be
added in tblExternalFileBackup table.
Alternative of OUTPUT Clause:
You can write a trigger on table to manage a log on a table. The benefits/ Limitation of triggers over OUTPUT clause are not in the scope of this blog.
Ref. Site: http://msdn.microsoft.com/en-us/library/ms177564(SQL.90).aspx
Prakash Software Solutions PVT LTD. Offer wide range of services to their clients all over the globe. We successfully delivered projects i.e. software development, cloud computing solutions, SharePoint development solutions, mobile app development and Microsoft technology solution to our Sweden, Denmark, Netherlands, France, Norway, Germany, Spain, Italy, Switzerland, Belgium, Luxembourg, Ireland, United Kingdom, United States, Canada, Brazil, Peru, UAE, Dubai, Saudi Arabia, Kuwait, Qatar, Bahrain, Singapore, New Zealand and Australia Clients.