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