Output Clause With Insert, Delete and Update Statement in SQL Server 2012

This article will give you an idea of how to use the Output clause and also defines where you can use it. SQL Server allows you to define an output clause. The output clause allows you to hold inserted, deleted and updated values during insert, delete and update DML operations on a table in SQL Server. So let's have a look at a practical example of how to use the Output Clause in SQL Server. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

It has access to the Inserted and Deleted table which is also called the magic tables. To see the Magic Table:

http://www.c-sharpcorner.com/UploadFile/rohatash/magic-tables-in-sql-server-2012/

Output Clause

If you have implemented an output clause for a table then:

1. In a DELETE query we can work with only DELETED
2. In an INSERT query we can work with only INSERTED
3. In a UPDATE query we can work with both INSERTED as well as DELETED

The output clause allows you to hold inserted, deleted and updated values during insert, delete and update DML operations on a table in SQL Server. The following are the examples.

Creating a table in SQL Server

Now we create a table named employee using:

Create table Employee

(

EmpID int,

EmpName varchar(30),

EmpSalary int

)

The following is the sample data for the employee Table:

Output Clause with Insert Statement

When you insert data into a table, you can use the OUTPUT clause to return a copy of the data that's been inserted into the table.

INSERT into

Employee

(

[EmpID],

EmpName,

EmpSalary