MERGE in SQL Server 2005

In my previous section I explained about OUTPUT feature in SQL Server. In this section I will go one step ahead and will tell you, how you can use this feature to implement MERGE in SQL Server 2005.

MERGE is a powerful feature in SQL Server 2008. If you want to know what it is check out the link http://www.sqlservercentral.com/articles/Advanced+Querying/3122/

Now coming back to the SQL Server 2005, with couple of SQL Statements you can easily implement this feature.

Before you continue further you need to check out my first post on the OUTPUT feature in SQL Server.

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);

Let’s create another table with the same structure but with out any data.

SELECT * INTO EMPLOYEE_WORKING_HRS_STG FROM EMPLOYEE_WORKING_HRS WHERE 1=2

Now insert the below data into this staging table.

INSERT INTO EMPLOYEE_WORKING_HRS_STG
([EMP_ID],[ENAME],[YR],[MON],[WORKING_HRS],[RATE],[SALARY])
VALUES
(105,'NICK',2009,'JAN',176,100,17600);
INSERT INTO EMPLOYEE_WORKING_HRS_STG
([EMP_ID],[ENAME],[YR],[MON],[WORKING_HRS],[RATE],[SALARY])
VALUES
(101,'LIMO',2009,'JAN',176,130,15840);

There are two records in this table. Now the task update EMPLOYEE_WORKING_HRS table based on the EMPLOYEE_WORKING_HRS_STG. If a record is not updated then INSERT that record into EMPLOYEE_WORKING_HRS table.

In this case 105 is new record so we have to insert this record into EMPLOYEE_WORKING_HRS table and update the 101 record with new Hourly Rate.

Now execute the below query

declare @Emp_ids table(ID int)
update EMPLOYEE_WORKING_HRS set RATE = s.RATE
output inserted.EMP_ID into @Emp_ids
from EMPLOYEE_WORKING_HRS m, EMPLOYEE_WORKING_HRS_STG s
WHERE s.EMP_ID = m.EMP_ID
INSERT INTO EMPLOYEE_WORKING_HRS
([EMP_ID],[ENAME],[YR],[MON],[WORKING_HRS],[RATE])
SELECT [EMP_ID],[ENAME],[YR],[MON],[WORKING_HRS],[RATE]
FROM EMPLOYEE_WORKING_HRS_STG WHERE EMP_ID NOT IN ( SELECT ID FROM @EMP_IDS)

This will update the EMPLOYEE_WORKING_HRS table where ever it finds the match in EMPLOYEE_WORKING_HRS_STG table and puts that EMP_ID that is the record which is affected with UPDATE statement and use this to copy data from EMPLOYEE_WORKING_HRS_STG table to EMPLOYEE_WORKING_HRS where EMP_ID is not in this updated IDs list.

That’s it. Its very simple isn’t it.

Some More articles related to this are:
OUTPUT in SQL Server 2005
Create Table Using Select in Sql Server