OUTPUT in SQL Server 2005

In this section, I would like to talk about one interesting feature of SQL Server 2005. Before I say anything about this feature let me ask you a simple question.

Can u write a single query to display all the records that are affected with an UPDATE statement?

It is impossible to find out this information with the UPDATE statement.

In most of the cases what we do is after the UPDATE we will go and query the table to see whether UPDATE ran successfully or not. I mean whether it updated the records which we are expecting.

This is two step process. What if there is a simple SQL statement that tells you what records are affected with the UPDATE statement? You will save lot of time with this.

Instead of waiting for some more time, the feature which I am talking about is OUTPUT keyword.

Let’s get straight into the example to understand what you need to do to get the updated records.

Quickly create the table:
CREATE TABLE EMPLOYEE_WORKING_HRS(
EMP_ID int NULL,
ENAME varchar(30) NULL,
YR varchar(4) NULL,
MON varchar(3) NULL,
WORKING_HRS int NULL,
RATE int NULL,
SALARY AS (WORKING_HRS*RATE)
)

Let’s insert few records into this table.
INSERT INTO EMPLOYEE_WORKING_HRS VALUES(100,'SAM',2009,'JAN',176,100);
INSERT INTO EMPLOYEE_WORKING_HRS VALUES (101,'LIMO',2009,'JAN',176,110);
INSERT INTO EMPLOYEE_WORKING_HRS VALUES(102,'NAT',2009,'JAN',176,120);
INSERT INTO EMPLOYEE_WORKING_HRS VALUES (103,'BECK',2009,'JAN',176,130);

Due to the financial crisis I want to Add the Hourly to -20 if it is greater than 100.

declare @Emp_ids table(ID int)
update EMPLOYEE_WORKING_HRS set RATE = RATE - 20
output inserted.EMP_ID into @Emp_ids
from EMPLOYEE_WORKING_HRS
WHERE RATE > 100
SELECT * FROM @Emp_ids

In this piece of code I declared a variable called @emp_ids which is of table type.
Then I have used UPDATE clause with INSERTED emp_id of table into @Emp_Ids table.

Finally selecting the data from @Emp_Ids table.

Isn’t it kool!!!!!

Next section I will talk about how to mimic the MERGE functionality in SQL SERVER 2005.