Chris Umbel

SQL 2008's MERGE statement

One of the coolest new features of SQL Server 2008 (and there are many) is the MERGE statement. This construct has existed in oracle and DB2 for a while now but it's new to SQL Server. Effectively it allows you to merge two tables together, combining UPDATE, INSERTS and DELETEs into one nifty package.

For the purposes of demonstration I'm going to provide a simple example that merges a table variable into a real table by updating rows with matching primary keys with the table variable's values. It will also insert into the real table where there's no match.

Consider the following table:

CREATE TABLE [dbo].[Things](
	[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] [varchar](16) NOT NULL
)
Go

With the following content

ID          Name
----------- ----------------
1           computer
2           box
3           keyboard
4           mouse
5           whiteboard
6           pencil
7           monitor
Then I'm going to create a table variable with some rows with a matching primary key and some rows that don't match:
declare @OtherThings table (ID int, Name varchar(255))

insert into @OtherThings (ID, Name)
values (6, 'pen')
insert into @OtherThings (ID, Name)
values (5, 'marker')

insert into @OtherThings (ID, Name)
values (NULL, 'notepad')
insert into @OtherThings (ID, Name)
values (NULL, 'lamp')

Our goal is to update the contents of the [Name] column for [ID] 5 to 'marker' and 6 to 'pen'. We also want two new rows to be created with [Name] values of 'notepad' and 'lamp'. We'll accomplish that with the following statement:

merge Things T /* Target Table */
using @OtherThings OT /* Source table */
on (OT.ID = T.ID) /* join expression, how to determine matches */
when matched then
	update set T.Name = OT.Name /* update all matching rows */
when not matched then
	insert (Name) values /* create rows that don't match */
		(OT.Name);

POW, our [Things] table now looks like:

ID          Name
----------- ----------------
1           computer
2           box
3           keyboard
4           mouse
5           marker
6           pen
7           monitor
8           notepad
9           lamp

Pretty simple, right? Note that the source table can be a table expression, and match conditions can be more complicated such as:

when matched and T.Name like '%c%' then

Naturally, there's more to it than I've illustrated here. Check out BOL for more details.

Tue Apr 22 2008 13:04:00 GMT+0000 (UTC)

Follow Chris
RSS Feed
Twitter
Facebook
CodePlex
github
LinkedIn
Google