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

  )

OUTPUT

 Inserted.[EmpID],

 Inserted.EmpName,

 Inserted.EmpSalary

VALUES

 (8, 'Delton', 15000);

Output

Output Clause with Delete Statement

When you delete data from a table, you can use the OUTPUT clause between the DELETE and WHERE clauses.

Delete from

 Employee

OUTPUT

 Deleted.EmpName,

 Deleted.EmpSalary

Where

 EmpId=5

Output

Output Clause with Update Statement

You can use both the INSERTED and DELETED column prefixes when adding an OUTPUT clause to an UPDATE statement.

UPDATE Employee

SET

 EmpName = 'Marry'

OUTPUT

 Inserted.EmpID,

 Inserted.EmpName

WHERE

 EmpID =8;

Output