Sql server 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 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: For the example here uses a 2 tables:
1. tblExternalFile: a parents Table
2. tblExternalFileBackup : a Backup Table
Structures of these 2 tables are bellow:
1. 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]
2. 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
Thanks,
Vivek Shah