OUTPUT Clause in SQL Server 2005

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:

  1. tblExternalFile: a parents Table
  2. tblExternalFileBackup : a Backup Table

Structures of these 2 tables are bellow:

  • tblExternalFile
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]
  • tblExternalFileBackup
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 Operation

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.

 

Leave a reply