My Adventures in Coding

January 5, 2016

SQL Server – How to write an Upsert using MERGE

Filed under: SQL,SQL Server — Brian @ 8:35 pm
Tags: , , , ,

Normally, when you want an application to INSERT a row to a table if it does not exist or UPDATE it if it does exist, your application must first do a SELECT to check if the row exists, which is the standard SELECT-UPDATE-INSERT pattern. The down side to this pattern is it requires two database round trips instead of one.

Now, over the years I have worked with document stores such as MongoDB and really enjoyed the freedom to be able to make a single call to the database and be able to ADD/REPLACE a document, without having to check if it exists.

Fortunately in SQL Server 2008, the MERGE function was introduced.

MERGE allows you to make only a single database round trip when you want to INSERT a row if it does not exist, or UPDATE it if it does. The following is a simple example showing how to use the MERGE statement.

Quick Start

So if you just want a quick example to get you started then here you go. In this example the table “ClientData” is the one being updated.

  • MERGE – specifies the table we will be inserting a row into or updating
  • USING – defines the condition we will be using to check if the row exists or not
  • WHEN MATCHED THEN – SQL statement to run when the row exists
  • WHEN NOT MATCHED – SQL statement to run when the row does not exist
MERGE dbo.ClientData AS [Target] 
USING (SELECT 12345 AS clientId, 'Some' AS data) AS [Source] ON [Target].clientId = [Source].clientId 
WHEN MATCHED THEN UPDATE SET [Target].data = [Source].data, [Target].updatedDateUtc = GetUtcDate() 
WHEN NOT MATCHED THEN INSERT (clientId, data) VALUES ([Source].clientId, [Source].data);

How it Works

First lets create a table to use for our test of the Merge statement:

CREATE TABLE dbo.ClientData(
	ClientId [bigint] NOT NULL,
	Data [varchar](20) NOT NULL,
	UpdatedDateUtc [datetime] NOT NULL DEFAULT (getutcdate()),
 CONSTRAINT [PK_ClientData_ClientId] PRIMARY KEY CLUSTERED (
	ClientId ASC
)) ON [PRIMARY]
GO

You can verify the table has been created and see that it is empty:

SELECT * FROM dbo.ClientData

Now, run the following Merge statement for the first time, where no matching row in the table:

MERGE dbo.ClientData AS [Target]
USING (SELECT 12345 AS clientId) AS [Source] 
ON [Target].clientId = [Source].clientId
WHEN MATCHED THEN  UPDATE SET [Target].data = 'Update', [Target].updatedDateUtc = GetUtcDate()
WHEN NOT MATCHED THEN  INSERT (clientId, data) VALUES ([Source].clientId, 'Insert');

As you can see, the INSERT statement was executed:

SELECT * FROM dbo.ClientData

Now let’s run the exact same merge statement a second time and see what happens:

MERGE dbo.ClientData AS [Target]
USING (SELECT 12345 AS clientId) AS [Source] 
ON [Target].clientId = [Source].clientId
WHEN MATCHED THEN  UPDATE SET [Target].data = 'Update', [Target].updatedDateUtc = GetUtcDate()
WHEN NOT MATCHED THEN  INSERT (clientId, data) VALUES ([Source].clientId, 'Insert');

Now, you can see the the UPDATE statement was executed since the “data” field has been updated to the text “Update”:

select * from dbo.ClientData

If you are curious about the performance difference between MERGE and SELECT-INSERT-UPDATE here is a performance comparison.

June 16, 2010

Working with XML in Scala: A Simple Example

Filed under: Conferences,Scala,UberConf 2010 — Brian @ 7:20 am
Tags: , , , ,

While at UberConf I had the great opportunity to attend a talk titled “Scala for Java Programmers” put on by Venkat Subramaniam, the author of the book “Scala Programming” . During the talk we went into a number of examples of where Scala excels over Java, in that it makes life for the developer significantly easier. One of those areas is in working with XML. In Scala, XML is a first class citizen (Woohoo!).

So for those of you who have never used Scala before, here are a few simple examples of working with XML:

Create XML
In this example all we do here is take some xml and assign it to a val. Okay, so does not seem like anything special, but wait, notice the lack of quotes around the XML tags.

val xml = <car make="Ford">Galaxy</car>
println(xml)

prints the XML:

<car make="Ford">Galaxy</car>

Generate XML
In this example we take a Map and generate XML based on the values in that Map.

val cars = Map("Galaxy" -> "Ford", "Chevelle" -> "Chevrolet")

def createCar() = {
   cars.map { entry =>
       val (key, value) = entry
       <car make={key}>{value}</car>
    }
}

val xml = <cars>{createCar()}</cars>
println(xml)

prints the XML:

<cars>
    <car make="Ford">Galaxy</car>
    <car make="Chevrolet">Chevelle</car>
</cars>

Parse XML with Xpath

val xml = <car make="Ford">Galaxy</car>
val make = xml \\ "@make"
println(make)

prints out the Result: Ford

Parsing XML From A Service
Alright, now let’s actually do something useful. The following is a simple example used by Venkat during his scala session. In this example we access Yahoo’s weather feed and return the City, State, and current temperature for Denver, CO.

import scala.xml._
import java.net._
import scala.io.Source

val theUrl = "http://weather.yahooapis.com/forecastrss?w=2391279&u=f"

val xmlString = Source.fromURL(new URL(theUrl)).mkString
val xml = XML.loadString(xmlString)

val city = xml \\ "location" \\ "@city"
val state = xml \\ "location" \\ "@region"
val temperature = xml \\ "condition" \\ "@temp"
 
println(city + " " + state + " " + temperature)

Now, for fun, try writing this example in Java (Hmm, okay, that won’t be much fun at all).

To Run the examples

Create a free website or blog at WordPress.com.