SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



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.

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 T-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






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems