ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

T-SQL (Transact-SQL) | MERGE (Inserting and Updating) | SQL Server

Updated on October 18, 2011

With SQL Server 2008 Microsoft introduced yet again a slew of new and exciting features including the MERGE.

Anybody who has worked with Oracle databases and queries knows how useful the Merge command (function) is. Alas, in the Sql Server (Transact-SQL or T-SQL for short) world this function was lacking for a long time.  

Using the Merge function is very similar the Merge in Oracle. For those who are new to the function, they will find it very straight forward and versatile. It is very easy to apply and can be used in a variety of ways. In the following article I will demonstrate a simple example followed by a more complex example..

General Syntax

The general syntax for using the Merge function in SQL Server is:
Merge Table definition as Target

Using ( Table Source ) as Source

(

Column Keys

)

ON (

Search Terms

)

WHEN MATCHED THEN

UPDATE SET

or

DELETE

WHEN NOT MATCHED BY TARGET THEN

INSERT

Although the syntax is extensive it is quite simple once you understand its construction. I always find something complicated until I actual do an exercise. So to demonstrate the Merge in a real scenario, I am going to do a couple of examples.

A Simple Example

Create SimpleTable Table(Fname varchar(50), LName varchar(50), Relationship varchar(50))
Merge SimpleTable as Target

Using (Select FirstName, LastName, Relationship from Contacts) as

Source

(

FirstName,

LastName,

Relationship

) ON

(

Target.FName = Source.FirstName AND

Target.LName = Source.LastName

)

WHEN MATCHED THEN

UPDATE SET TARGET.Relationship = Source.Relationship
WHEN NOT MATCHED BY TARGET THEN

INSERT (FName, LName, Relationship)

VALUES (

FirstName, LastName, Relationship

);

The SimpleTable definition can be any table including a Temporary Table, Table Variable or a Regular Table. First you need to declare the Target table. This is what I am doing with the first line:

Using (Select FirstName, LastName, Relationship from Contacts) as

Source( FirstName, LastName, Relationship)

Next you need to define the source table that will supply the data. You can only include the same columns as the Target table but the Source table can have more columns just don’t include them in the Source definition.

ON

(

Target.FName = Source.FirstName AND

Target.LName = Source.LastName

)

This defines the columns that will be used as the keys

WHEN MATCHED THEN

UPDATE SET TARGET.Relationship = Source.Relationship

When a match is found do something. This usually includes an Update or a Delete operation.

WHEN NOT MATCHED BY TARGET THEN

INSERT (FName, LName, Relationship)

VALUES (

FirstName, LastName, Relationship

);

When a match is not made, then perform an Insert. The Match and Not Match operations can be as complex as needed. There is no restrictions here. The same goes for any other operation within the Merge.

A Complex Example Using MERGE and OPENROWSET

The next example will be more complicated as I will use an Excel file as the source and split the Amount column into Sales or Budget based on the TransactionType column. The split operation will accomplished using a simple Case WHEN and I will also calculate a variance between Sales and Budget as a dollar value and as a percentage.


First I will seed the Sales table with some values.

declare @n int

set @n = 0
While @n < 10

Begin

Insert into Sales(ProductCategory, ProductName, Sales, Budget, Variance, VarPercentage)

Values(‘Business’,'Computer’, 500+@n, 550, -50, (550 -50)/550)

Insert into Sales(ProductCategory, ProductName, Sales, Budget, Variance, VarPercentage)

Values(‘Business’,'Computer’,2000+@n, 1500, 500, (1500-500)/1500 )

set @n = @n + 1

end

The Excel file needs to be in a location that the user you are using has read/write permission to.

Next I am going the Merge operation

Merge Sales as [Target]

Using

(Select ProductCategory, ProductName,

sum(case when TransactionType = ‘Sales’ then Amount else 0 end) as Sales,

sum(case when TransactionType = ‘Budget’ then Amount else 0 end) as Budget

from OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′,

‘Excel 12.0;Database=C:\GizmoSales.xlsx;Extended Properties=”Excel 12.0 Xml;HDR=YES”;’, ‘Select * from [SalesData$]‘)

group by ProductCategory, ProductName) as [Source]

ON
[Target].ProductCategory=Source.ProductCategory AND

[Target].ProductName=Source.ProductName
WHEN MATCHED THEN

UPDATE SET [Target].Variance = Source.Sales – Source.Budget,

[Target].VarPercentage = (Source.Budget – (Source.Sales – Source.Budget))/Source.Budget
WHEN NOT MATCHED BY TARGET THEN

INSERT ([Target].ProductCategory, [Target].ProductName, [Target].Sales, [Target].Budget, [Target].Variance, [Target].VarPercentage

VALUES (

Source.ProductCategory, Source.ProductName, Source.Sales, Source.Budget, Source.Sales – Source.Budget, (Source.Budget – (Source.Sales – Source.Budget))/Source.Budget

);

The Target table can be any type of table as well as the source table. In the above example I am using the “Microsoft.ACE.OLEDB.12.0” which is required for the SQL Server 2008. In previous releases the provider was “Microsoft.Jet.OLEDB.4.0”. Also you need to add the “Extended Properties” argument and the HDR arguments “Excel 12.0 Xml;HDR=YES”; to tell the provider that the source is an Excel file and not an Access database, which is the default. Notice also the syntax for the Excel Worksheet name “[SalesData$]” even if the name in the sheet is actually “SalesData”.

Conclusion


The Merge function can be used in a variety of scenarios, instead of having used an Excel file as a source, I could have used input variables in a stored procedure as a result of some pre processing temporary table or variable table.

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    working

    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, hubpages.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://hubpages.com/privacy-policy#gdpr

    Show Details
    Necessary
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
    Features
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Marketing
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Statistics
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)