Output and Output Into clause in SQL Server 2005

SQL Server 2005 introduced new OUTPUT and OUTPUT INTO Clause for showing the result after the Insert/Update/Delete statement in single go. Earlier (SQL Server 2000), we are using two T-SQL statement for getting result after Insert/Update/Delete statement.
For example we have a table “tbl_Employee” and it has three columns (ID, Name, Salary).
Create table tbl_Employee
(
EmpID Int,
EmpName varchar(50),
Salary Decimal(10,2)
)
Example
In SQL Server 2000, first we execute Insert/Update/Delete statement, and then we execute the select statement to see the result.
Example with Insert Statement.
Insert Into tbl_Employee (EmpID, EmpName, Salary)
Values (1, ‘AAA’, 5000)
Go
Select * from tbl_Employee
Go
The same thing we do with UPDATE and DELETE statement in SQL Server 2000. First we update or Delete the record and then see the output (result).
But in SQL Server 2005, there is an OUTPUT clause for providing output (result) after performing Insert/Update/Delete action in single step (in single query).
OUTPUT Clause with INSERT Statement
Insert into tbl_Employee (EmpID, EmpName, Salary)
OUTPUT Inserted.*
Values (1, 'AAA', 3000)
Go
OUTPUT INTO Clause with INSERT Statement
If you want to insert the output (result) in different table, use OUTPUT INTO clause with other table name. We have another table named tbl_EmployeeLog with same column name and data type of tbl_Employee table.

Insert into tbl_Employee (EmpID, EmpName, Salary)
OUTPUT Inserted.* INTO tbl_EmployeeLog
Values (2, 'BBB', 3000)
Go

If you see the record of tbl_EmployeeLog table, you will get following record.







But don’t start to think that you would use OUTPUT INTO clause for maintaining log. This is good option for inserting output in another table, but for maintaining the log, TRIGGER is the best option. Always use TRIGGER wherever you need to maintain the log of any (Insert/Update/Delete) action.