In my humble opinion, one of the best features of SQL 2008 is the MERGE command. In a nutshell, the MERGE statement allows you to insert, update, or delete data based on certain join conditions in a single statement. Traditionally this type of work was accomplished by creating a cursor, looping over each row and running some conditional logic to determine if you needed to insert, update or delete some rows.
If you view the syntax for the MERGE command with SQL books online you may be a little overwhelmed. The 100+ line syntax sample is not easy on the eyes. Therefore I was afraid that some people may be discouraged from using it, and I felt obligated to give an example that is a little easier to understand. First we will start by creating two tables and mocking up some data to test the MERGE command with:
1: CREATE Table Planets (
2: ID int not null,
3: Name varchar(25) not null,
4: Comments varchar(50) null
5: )
6: GO
7: CREATE Table MorePlanets (
8: ID int not null,
9: Name varchar(25) not null,
10: Comments varchar(50) null
11: )
12: GO
13: INSERT INTO Planets
14: VALUES
15: (1, 'Mercury', 'Smallest planet in our solar system.'),
16: (2, 'Venus', 'Named after the Roman goddess of love.'),
17: (3, 'Earth', ''),
18: (4, 'Mars', 'The red planet.' ),
19: (5, 'Jupiter', '' ),
20: (6, 'Saturn', 'Not the car company.' )
21: GO
22: INSERT INTO MorePlanets
23: VALUES
24: (3, 'Earth', 'All your base belong to us.'),
25: (5, 'Jupiter', 'The largest plant in our solar system.' ),
26: (7, 'Uranus', 'I know what your thinking!' ),
27: (8, 'Neptune', 'The blue giant.' ),
28: (9, 'Pluto', 'Not Mickey''s dog' )
29:
30: select * from Planets
31: select * from MorePlanets
Note: In SQL 2008 we can also insert multiple values into a table with a single insert command!
You should end up with two tables, once called Planets and another named MorePlanets. If you observe the row data you will find that two records (Earth and Jupiter) exist in both tables.
There are also 3 additional records in the MorePlanets table that do not exist in the Planets table yet. The goal of our MERGE statement will be to update the name and description fields in the Planets table where the ID's match and to insert the records that have no corresponding ID:
1: MERGE Planets as target
2: USING( SELECT ID, Name, Comments FROM MorePlanets ) AS source
3: ON( target.ID = source.ID )
4: WHEN MATCHED THEN
5: UPDATE set target.Name = source.Name, target.Comments = source.Comments
6: WHEN NOT MATCHED THEN
7: INSERT
8: (ID, Name, Comments )
9: VALUES
10: ( source.ID, source.Name, source.Comments );
11:
12: select * from Planets
Lets break down this sample MERGE statement line by line:
- Line 1: We pick the Planets table as the merge target. This is where the merged results will end up
- Line 2: We use a select statement as the source. In this example our tables have the exact same column names and data types so it makes things easy.
- Line 3: We defined the matching criteria for the merge. In this case the IDs of the tables serve as the unique identifier.
- Line 4-5: For each record in the MorePlanets table that has an ID that exists in the Planets table we will update the name and comments field.
- Lines 6-10: For each record in the MorePlanets table that has an ID that does not exist in the Planets table we will insert a new row.
After we run the merge statement, here are the results:
Pretty cool eh? As you can see this command is very powerful. As a developer, I can think of a lot of uses for this command. Now that you understand the basic capabilities of the MERGE command spend some time reviewing Microsoft's examples. They show the advanced uses of the MERGE statement.
