SQL Server T-SQL OUTPUT Clause Sample Code with Insert, Update, Delete Statements
SQL 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 was first introduced with Microsoft SQL Server 2005 to SQL developers.
T-SQL Output clause in SQL Server makes it possible to return a list of rows while insert, update or delete data operations. This is like a miracle, to do a DML operation and a SELECT at the same time. The first usage area of sql output clause that comes to mind is for logging operations. When a data is deleted for example, you can move original data into a log table in your SQL database.
Just like using a SQL trigger, within sql Output clause syntax developers can fetch new data in Inserted temp table and old data in Deleted temporary table.
Even with this feature SQL Output clause is my favourite candidate to be used more common instead of triggers in SQL Server database applications.
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
The following t-sql script is used to insert data into a database table. But while inserting data operation the auto generated identity column or Id value is selected with new tsql Output clause into a temporary sql table. And this Inserted Id value is read from sql temporary table into a sql variable.
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
This SQL select script with Output clause syntax is showing how to log inserted row values in a temporary table.
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
Using the following sql script, database developers can insert data into two sql tables at the same time. This usage can be an alternative to SQL Server After Insert triggers.
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
In this T-SQL Delete command, SQL Output clause enables deleted data to be archived into a sql database log table without using a SQL Server trigger. The Output clause in Delete statement in the below example simplifies SQL developers' tasks to log deleted data without building complex queries and sql structures.
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
Output Clause T-SQL Sample Code with UPDATE Statement using variable tables
Here in this sample Update statement with SQL Server Output clause, sql developers can insert new values into a temporary table and later select this new updated row values from temporary table.
DECLARE @updates TABLE
(
code nvarchar(20),
[description] nvarchar(max)
)
UPDATE [Output Clause Sample Table]
SET
code = N'MS SQL Server 2005',
[description] = N'Enhancements in T-SQL introduced with Microsoft SQL Server 2005'
OUTPUT INSERTED.code, INSERTED.[description]
INTO @updates
WHERE id = 2
SELECT * FROM @updates
For more T-SQL examples and tutorials on SQL Output Clause please refer to T-SQL OUTPUT Clause in order to INSERT Data in Two Tables in One Command and Sample SQL Column Identity Generator using OUTPUT CLAUSE in T-SQL and SPARSE NULL Data Type