SQL Tips by Namwar Rizvi

August 27, 2007

MERGE Statement of SQL Seerver 2008

Filed under: Information,New Features,Performance,SQL Server 2008,TSQL — namwar @ 10:28 PM

SQL Server 2008 has introduced a new statement called MERGE which will combine the logic of INSERT-IF NOT EXITS and UPDATE-IF EXISTS.
MERGE statement will give you the ability to write one single statement which will update the row if it already exists otherwise it will insert a new row.
Additionally, MERGE statement also enable you to combine the logic of delete if you want.
Following example from Microsfot SQL Server 2008 Books Online
will give you the better idea:

Use AdventureWorks
Go

MERGE Departments AS d
USING Departments_delta AS dd

ON (d.DeptID = dd.DeptID)
WHEN MATCHED AND d.Manager dd.Manager
OR d.DeptName dd.DeptName
THEN UPDATE SET d.Manager = dd.Manager,
d.DeptName = dd.DeptName

WHEN NOT MATCHED THEN
INSERT (DeptID, DeptName, Manager)
VALUES (dd.DeptID, dd.DeptName, dd.Manager)

WHEN SOURCE NOT MATCHED THEN
DELETE;

Advertisements

1 Comment »


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: