Click here to Skip to main content
15,867,935 members
Articles / Database Development / SQL Server / SQL Server 2008

Using the MERGE Statement in SQL Server 2008

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
31 Jul 2011CPOL1 min read 19.9K   6  
How to use the new MERGE statement feature in SQL Server 2008

MERGE’ statement is a new feature in SQL Server 2008. It can be used to perform insert, update and delete operation on a destination table simultaneously based on the results of a join with a source table. Well, it sounds a bit confusing, but let's see an example of how it can help us.

Assume we have the following two tables:

  • STUDENT_A
  • STUDENT_B

Both tables are identical in structure (Structure does not need to be identical).

STUDENT_A

img_scr_001

STUDENT_B

img_scr_002

And we have to update ‘STUDENT_A’ with the details of ‘STUDENT_B’. We need to compare and if student IDs are matched, ‘A’ table should be updated with the ‘B’ table. And if the IDs in ‘B’ Table are new, then we have to insert those to the ‘A’ table.

img_scr_003

So using the ‘MERGE’ statement, we can achieve this in one execution.

Syntax:

SQL
MERGE  <Target> [AS T]
USING    <Source> [AS S]
ON <Condition>
[WHEN MATCHED THEN <Execution>]
[WHEN NOT MATCHED BY TARGET <Execution>]
[WHEN NOT MATCHED BY SOURCE <Execution>]

And to do the above operation, use the following code:

SQL
MERGE STUDENT_A AS T
USING STUDENT_B AS S
ON T.ID = S.ID
WHEN MATCHED THEN UPDATE SET T.AGE = S.AGE
WHEN NOT MATCHED THEN INSERT (ID, FNAME, LNAME, AGE) VALUES(S.ID,S.FNAME,S.LNAME,S.AGE);

**Please note that semicolon ‘;’ is mandatory.

So after executing the above code, and if you inspect the Table ‘A’, you can see that it’s updated the way we wanted.

img_scr_005

And also, you can use additional rules other than your condition. To illustrate that, first we insert a record to both the tables.

SQL
insert into STUDENT_A 
select 10, 'John','Doe',30

insert into STUDENT_B 
select 10, 'John','Doe',30

And using the following code, you can remove the record which matches the condition and have the value 10.

SQL
MERGE STUDENT_A AS T
USING STUDENT_B AS S
ON T.ID = S.ID
WHEN MATCHED and S.ID < 5 THEN UPDATE SET T.AGE = S.AGE
WHEN MATCHED and S.ID = 10 THEN DELETE
WHEN NOT MATCHED BY TARGET THEN INSERT (ID, FNAME, LNAME, AGE) 
         VALUES(S.ID,S.FNAME,S.LNAME,S.AGE);

And if you inspect the table A, you can see that it has the same following results:

img_scr_005

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Technical Lead Air Liquide Industrial Services (Singapore)
Singapore Singapore
My passion lies in building business intelligence and data-based solutions, writing about things I work with and talking about it. New technologies relevant to my line of work interest me and I am often seen playing with early releases of such technologies.

My current role involves architecting and building a variety of data solutions, providing database maintenance and administration support, building the organization’s data practice, and training and mentoring peers.

My aspiration over the next several years is to achieve higher competency and recognition in the field of Data Analytics and move into a career of data science.


Specialities: SQL Server, T-SQL Development, SQL Server Administration, SSRS, SSIS, C#, ASP.Net, Crystal Reports

Comments and Discussions

 
-- There are no messages in this forum --