MS SQL Server 2005 T-SQL OUTPUT Clause Sample Code with Insert, Update, Delete Statements

Output clause extension to INSERT, UPDATE and DELETE T-SQL statements enables sql programmers or database developers to store inserted, updated or deleted values in a transaction by the help of inserted and deleted tables into temporary tables, table variables or into database tables.
OUTPUT clause is a new enhancement in t-sql with Microsoft SQL Server 2005.

Here are some t-sql code samples which use new OUTPUT clause extension used with Insert, Update and Delete statements.

Output Clause Sample Code with Insert Statement and table variables

GO

CREATE TABLE [Output Clause Sample Table]
(
id int identity(1,1),
code nvarchar(20),
[description] nvarchar(max)
)

GO

declare @id table
(
id int
)
declare @idval int

INSERT INTO [Output Clause Sample Table]
(
Code,
[Description]
)
OUTPUT INSERTED.id
INTO @id(id)
VALUES (
N'Output Clause',
N'Output Clause T-SQL Sample with INSERT Statement'
)

SELECT * FROM [Output Clause Sample Table]
SELECT * FROM @id
SELECT @idval = id FROM @id
SELECT @idval


Using "*" (ALL) with INSERTED.* syntax in the OUTPUT Clause Sample T-SQL Code

CREATE TABLE [Output Clause Sample Table]
(
id int identity(1,1),
code nvarchar(20),
[description] nvarchar(max)
)

GO

declare @id table
(
id int,
code nvarchar(20),
[description] nvarchar(max)
)
declare @idval int

INSERT INTO [Output Clause Sample Table]
(
Code,
[Description]
)
OUTPUT INSERTED.*
INTO @id
VALUES (
N'Output Clause',
N'Output Clause T-SQL Sample with INSERT Statement'
)

SELECT * FROM [Output Clause Sample Table]
SELECT * FROM @id
SELECT @idval = id FROM @id
SELECT @idval


Output Clause Sample Code with Insert Statement and database tables

GO

CREATE TABLE Output_Table
(
id int,
code sysname
)
GO

INSERT INTO [Output Clause Sample Table]
(
Code,
[Description]
)
OUTPUT INSERTED.id, Inserted.Code
INTO Output_Table(id, code)
VALUES (
N'MS SQL 2005',
N'T-SQL Enhancements with Microsoft SQL Server 2005'
)

SELECT * FROM [Output Clause Sample Table]
SELECT * FROM Output_Table


Output Clause T-SQL Sample Code with DELETE Statement, temporary, variable tables and database tables

GO

declare @id table
(
id int
)
GO

DELETE FROM [Output Clause Sample Table]
OUTPUT DELETED.id
INTO @id(id)
WHERE Id = 1

DELETE FROM [Output Clause Sample Table]
OUTPUT DELETED.id, DELETED.Code, DELETED.Description
INTO [Output Clause Sample Table Archive](Id, Code, [Description])
WHERE Id = 1

SELECT * FROM [Output Clause Sample Table]
SELECT * FROM Output_Table


BlinkList   Del.icio.us   Digg   Furl   Simpy   Spurl   DZone   ma.gnolia   Shadows