Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Using the MERGE statement in SQL Server 2008

, 31 Jul 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
Using 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 like a bit confusing, but let's see an example on how it can help us.

Assume we have following two tables.

  • STUDENT_A
  • STUDENT_B

Both table 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 the ‘STUDENT_A’ with the details at ‘STUDENT_B’. We need to compare and if student ID’s are matched, ‘A’ table should be updated with the ‘B’ table. And if the ID’s 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:

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:

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.

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 with matches the condition and have the value 10.

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)

Share

About the Author

Manjuke Fernando
Technical Lead Air Liquide Industrial Services (Singapore)
Singapore Singapore
I have been in software industry for more than 8 years. I have developed different type of software using different languages. Many of them are database related (both web & window based), SQL being as the back end most of the time. Up-to-date I have knowledge in languages such as C#, VB.Net, T-SQL, JAVA, VB6 & C++, making C# the most proficient of all. Also I have worked using different technologies like ASP.Net, SharePoint, Crystal Reports (But I really hate designing reports) & MS SQL Server and have involved in designing & developing software for major companies like FedEx, Softlogic Holdings, IronOne Technologies & Brandix. Currently I am working as a Tech Lead in Singapore.
Follow on   Twitter   LinkedIn

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141220.1 | Last Updated 31 Jul 2011
Article Copyright 2011 by Manjuke Fernando
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid