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

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.

More by this Author


Comments

No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working