using sql server 2008's merge statement tech republic

Using SQL Server 2008's MERGE statement | TechRepublic

http://www.techrepublic.com/blog/datacenter/using-sql-server-2008s-merge-statement/194[08/29/2012 3:20:12 PM]

Blogs Downloads Newsletters Galleries Q&A Discussions News

Research Library

Home / Blogs / The Enterprise Cloud

The Enterprise Cloud

Using SQL Server 2008'sMERGE statementBy Tim ChapmanSeptember 24, 2007, 12:09 PM PDT

Takeaway: SQL Server 2008s new MERGE construct allows you to insert, update, or deletedata based on certain join conditions in the same statement. Tim Chapman shows you howMERGE works with a hands-on example.

SQL Server 2008s new MERGE statement allows you to insert, update, or delete data based oncertain join conditions in the same statement. In previous versions of SQL Server, you have tocreate separate statements if you need to insert, update, or delete data in one table based oncertain conditions in another table. With MERGE, you can include the logic for these datamodifications in one statement.

How MERGE worksThe MERGE statement basically works as separate insert, update, and delete statements allwithin the same statement. You specify a Source record set and a Target table, and the joinbetween the two. You then specify the type of data modification that is to occur when the recordsbetween the two data are matched or are not matched. MERGE is very useful, especially when itcomes to loading data warehouse tables, which can be very large and require specific actions tobe taken when rows are or are not present.

MERGE exampleI will simulate sales feeds being received in the database and loaded to a reporting table thatrecords daily sales statistics. In a typical scenario, the records would be loaded into a staging table(SalesFeed in this example), and then a series of transformations or DDL statements would beexecuted on the reporting table (SalesArchive in this example) to update the daily sales data. TheMERGE statement allows you to use one statement to update the SalesArchive table rather thanuse several different DDL statements, which potentially could reduce the time it takes to make theupdates occur, since only one lookup is done on the data rather than several.

The following script creates the SalesArchive and SalesFeed tables:

CREATE TABLE SalesArchive ( CustomerID INT PRIMARY KEY, SalesDate INT, TotalSalesAmount MONEY, TotalSalesCount SMALLINT, CreationDate DATETIME CONSTRAINT df_CreationDate DEFAULT(GETDATE()), UpdatedDate DATETIME CONSTRAINT df_UpdatedDate DEFAULT(GETDATE()) )

Follow this blog:

IT Management Development IT Support Data Center Networks Security

Log In Join TechRepublic FAQ Go Pro!ZDNet Asia SmartPlanet TechRepublic

http://ad.doubleclick.net/click;h=v8/3ce0/0/0/%2a/d;44306;0-0;0;74341537;31-1/1;0/0/0;;~sscs=%3fhttp://www.techrepublic.com/http://www.techrepublic.com/http://www.techrepublic.com/blogshttp://www.techrepublic.com/downloadshttp://www.techrepublic.com/newslettershttp://www.techrepublic.com/photoshttp://www.techrepublic.com/forum/questionshttp://www.techrepublic.com/forum/discussionshttp://www.techrepublic.com/newshttp://www.techrepublic.com/research-libraryhttp://www.techrepublic.com/http://www.techrepublic.com/blogshttp://www.techrepublic.com/blog/datacenterhttp://ad.doubleclick.net/click;h=v8/3ce0/0/0/%2a/j;44306;0-0;0;74341537;3823-300/100;0/0/0;;~sscs=%3fhttp://www.techrepublic.com/search?q=tim+chapmanhttp://www.techrepublic.com/blog/datacenter?mode=rsshttp://www.techrepublic.com/alerts/add?url=http%3A%2F%2Fwww.techrepublic.com%2Fblog%2Fdatacenter%3Fmode%3Drss&title=The+Enterprise+Cloud+on+TechRepublic&source=http%3A%2F%2Fwww.techrepublic.com%2Fblog%2Fdatacenter%2Fusing-sql-server-2008s-merge-statement%2F194&frequency=weekly&rss_type=38http://www.techrepublic.com/members/login?regSrc=global-reghttp://www.techrepublic.com/members/join?regSrc=global-reghttp://www.techrepublic.com/faq/generalhttp://www.techrepublic.com/prohttp://www.zdnetasia.com/http://www.smartplanet.com/http://www.techrepublic.com/



CREATE TABLE SalesFeed ( CustomerID INT, Product VARCHAR(10), SaleAmount MONEY )

The script below loads some data into the SalesFeed table. The way in which I am inserting datainto this table is new to SQL Server 2008; it allows you to specify many values to be insertedusing the VALUES clause of the INSERT statement.

INSERT INTO SalesFeed (CustomerID, Product, SaleAmount) VALUES (1,'PoolTable', 1000), (2,'BigScreen', 955), (3,'Computer', 590), (4,'BigScreen', 880), (5,'Computer', 700)

I have a few rows of data in my SalesFeed table and no data in my SalesArchive table. Now it istime for me to create my MERGE statement to add data to this table. Below is the MERGE script.

MERGE SalesArchive AS SA USING ( SELECT CustomerID, LoadDate = MIN(CONVERT(VARCHAR(8), GETDATE(), 112)), TotalSalesAmount = SUM(SaleAmount), TotalSalesCount = COUNT(*) FROM SalesFeed GROUP BY CustomerID ) AS SalesFeedCTE (CustomerID, LoadDate, TotalSalesAmount, TotalSalesCount) ON ( SA.CustomerID = SalesFeedCTE.CustomerID AND SA.SalesDate = SalesFeedCTE.LoadDate ) WHEN NOT MATCHED THEN INSERT (CustomerID, SalesDate, TotalSalesAmount, TotalSalesCount, CreationDate, UpdatedDate) VALUES( SalesFeedCTE.CustomerID, SalesFeedCTE.LoadDate, SalesFeedCTE.TotalSalesAmount, SalesFeedCTE.TotalSalesCount, GETDATE(), GETDATE()) WHEN MATCHED THEN UPDATE SET SA.TotalSalesAmount = SA.TotalSalesAmount + SalesFeedCTE.TotalSalesAmount, SA.TotalSalesCount = SA.TotalSalesCount + SalesFeedCTE.TotalSalesCount, SA.UpdatedDate = GETDATE();

At first glance, it looks reasonably complicated, but its not too bad once you get used to it. Thetable immediately following the MERGE statement is the table that will be modified; this is knownas the TARGET table. In the USING statement, data from the SalesFeed table is beingaggregated inside of a subquery based on the CustomerID; this portion is known as the SOURCE.This aggregation allows me to guarantee that there will be only one record per customer to updatemy SalesArchive table.

The ON clause of the MERGE statement is where I specify: the joining between the SOURCE, theaggregated data from the subquery, and the TARGET, the SalesArchive table.

The WHEN NOT MATCHED clause is where I specify what action I want to occur when therecords from the SOURCE are not found in the TARGET. In this scenario, I want to insert thoserecords into the SalesArchive table.

The WHEN MATCHED clause is where I specify what I need to occur when the records from theSalesArchive table and the subquery of the SalesFeed table are found. In this scenario, I want toupdate what is currently in the table for that day, such as the TotalSalesAmount, theTotalSalesCount, and the UpdatedDate.

With this scenario, if another sales feed comes into the database, only one statement will need tobe run for that feed. Any new customer sales will be added to the database, and any existingsales will be updated with the new sales information.

Tim Chapman a SQL Server database administrator and consultant who works for a bank inLouisville, KY. Tim has more than eight years of IT experience, and he is a Microsoft certifiedDatabase Developer and Administrator. If you would like to contact Tim, please e-mail him at

http://techrepublic.com.com/5213-6257-0.html?id=4492495&redirectTo=%2f1320-22-20.html



12Comments

Add Your Opinion

See All CommentsMy Contacts

Get IT Tips, news, and reviews delivered directly to your inbox by subscribing to TechRepublics freenewsletters.

About Tim Chapman

[email protected].

Get SQL tips in your inboxTechRepublics free SQL Server newsletter, delivered each Tuesday, contains hands-on tips thatwill help you become more adept with this powerful relational database management system.Automatically subscribe today!

Join the conversation!

Full Bio Contact

Storage considerations forvirtual desktops

Recover lost Windows Server2003 files with VolumeShadow Copy

Follow via:

Staff Picks Top Rated Most Recent

View in thread

Can you merge tables across DB's same server?NewToTheGame Updated - 14th Jul 2010

Trying to find out if it is possible to merge table data from table on another DB onsame server

What syntax is required?

All web examples us tables in same DB

0Votes

You're really not going to get around the cost incurred to run the separateinsert/update/delete statements. I haven't ran any significant tests for this myself, but Idon't think that the advantages... Read Whole Comment +

View in thread

Re: Performancechapman.tim@... 25th Sep 2007

0Votes

While I agree the table may be scanned once, the MERGE statement still invokes

Performancerichnorgaard@... 25th Sep 2007

0Votes

http://www.techrepublic.com/forum/discussions/102-238593http://www.techrepublic.com/newslettershttp://www.techrepublic.com/newslettersmailto:[email protected]://nl.com.com/MiniFormHandler?brand=techrepublic&list_id=e046http://www.techrepublic.com/blog/datacenter/storage-considerations-for-virtual-desktops/193http://www.techrepublic.com/blog/datacenter/storage-considerations-for-virtual-desktops/193http://www.techrepublic.com/blog/datacenter/storage-considerations-for-virtual-desktops/193http://www.techrepublic.com/blog/datacenter/recover-lost-windows-server-2003-files-with-volume-shadow-copy/177http://www.techrepublic.com/blog/datacenter/recover-lost-windows-server-2003-files-with-volume-shadow-copy/177http://www.techrepublic.com/blog/datacenter/recover-lost-windows-server-2003-files-with-volume-shadow-copy/177http://www.techrepublic.com/blog/datacenter/recover-lost-windows-server-2003-files-with-volume-shadow-copy/177http://www.techrepublic.com/forum/discussions/102-238593/rsshttp://www.techrepublic.com/alerts/add?url=http%3A%2F%2Fwww.techrepublic.com%2Fforum%2Fdiscussions%2F102-238593%2Frss&title=Discussion+on+Using+SQL+Server+2008%27s+MERGE+statement+on+TechRepublic&source=http%3A%2F%2Fwww.techrepublic.com%2Fblog%2Fdatacenter%2Fusing-sql-server-2008s-merge-statement%2F194&frequency=weekly&rss_type=42http://www.techrepublic.com/forum/discussions/102-238593-3323886http://www.techrepublic.com/members/profile/670500http://www.techrepublic.com/util/votehttp://www.techrepublic.com/util/votehttp://www.techrepublic.com/forum/discussions/102-238593-2325927http://www.techrepublic.com/members/profile/4492495http://www.techrepublic.com/util/votehttp://www.techrepublic.com/util/votehttp://www.techrepublic.com/members/profile/4788392http://www.techrepublic.com/util/votehttp://www.techrepublic.com/util/vote



Join Login

See all comments

Join the TechRepublic Community and join the conversation! Signing-up isfree and quick, Do it now, we want to hear your opinion.

both an INSERT and UPDATE. The optimizer may build a special plan to leveragethe lookup, but in the end the INSERT and... Read Whole Comment +

View in thread

http://www.techrepublic.com/forum/discussions/102-238593http://www.techrepublic.com/members/join?regSrc=disc-starthttp://www.techrepublic.com/members/join?regSrc=disc-starthttp://www.techrepublic.com/members/login?regSrc=disc-starthttp://www.techrepublic.com/members/login?regSrc=disc-starthttp://www.techrepublic.com/forum/discussions/102-238593http://www.techrepublic.com/forum/discussions/102-238593-2325925

techrepublic.comUsing SQL Server 2008's MERGE statement | TechRepublic

hzLW1lcmdlLXN0YXRlbWVudC8xOTQA: form1: q: button3:

using sql server 2008's merge statement tech republic

Documents