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.

January 2, 2016

Java – Simple GZIP Utility to Compress and Decompress a String

Filed under: Java — Brian @ 10:15 pm
Tags: , , ,

I wanted to have a simple utility class in our app so we could easily compress a String to a byte[] when our REST API received a GET request with the header “Accept-Encoding: gzip” and also be able to decompress a byte[] to a String when we received a PUT with the header “Content-Encoding: gzip”. So I wrote a simple utility class to GZIP a String to a byte[] and to unzip a GZIP byte[] to a String.

So here is a simple GzipUtil class:

import java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.zip.GZIPInputStream;
import java.util.zip.GZIPOutputStream;

public class GzipUtil {

    public static byte[] zip(final String str) {
        if ((str == null) || (str.length() == 0)) {
            throw new IllegalArgumentException("Cannot zip null or empty string");
        }

        try (ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream()) {
            try (GZIPOutputStream gzipOutputStream = new GZIPOutputStream(byteArrayOutputStream)) {
                gzipOutputStream.write(str.getBytes(StandardCharsets.UTF_8));
            }
            return byteArrayOutputStream.toByteArray();
        } catch(IOException e) {
            throw new RuntimeException("Failed to zip content", e);
        }
    }

    public static String unzip(final byte[] compressed) {
        if ((compressed == null) || (compressed.length == 0)) {
            throw new IllegalArgumentException("Cannot unzip null or empty bytes");
        }
        if (!isZipped(compressed)) {
            return new String(compressed);
        }

        try (ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(compressed)) {
            try (GZIPInputStream gzipInputStream = new GZIPInputStream(byteArrayInputStream)) {
                try (InputStreamReader inputStreamReader = new InputStreamReader(gzipInputStream, StandardCharsets.UTF_8)) {
                    try (BufferedReader bufferedReader = new BufferedReader(inputStreamReader)) {
                        StringBuilder output = new StringBuilder();
                        String line;
                        while((line = bufferedReader.readLine()) != null){
                            output.append(line);
                        }
                        return output.toString();
                    }
                }
            }
        } catch(IOException e) {
            throw new RuntimeException("Failed to unzip content", e);
        }
    }

    public static boolean isZipped(final byte[] compressed) {
        return (compressed[0] == (byte) (GZIPInputStream.GZIP_MAGIC)) && (compressed[1] == (byte) (GZIPInputStream.GZIP_MAGIC >> 8));
    }
}

Also here are a set of JUnit tests for this utility:

import org.junit.Test;
import static org.junit.Assert.*;

public class GzipUtilTest {

    @Test(expected = IllegalArgumentException.class)
    public void zip_shouldThrowIllegalArgumentException_whenStringToCompressIsNull() {
        GzipUtil.zip(null);
    }

    @Test(expected = IllegalArgumentException.class)
    public void zip_shouldThrowIllegalArgumentException_whenStringToCompressIsEmpty() {
        GzipUtil.zip("");
    }

    @Test
    public void zip_shouldGzipString_whenStringIsNotEmpty() {
        String xml = "<Hello>World</Hello>";

        byte[] actual = GzipUtil.zip(xml);

        assertTrue(GzipUtil.isZipped(actual));
    }

    @Test(expected = IllegalArgumentException.class)
    public void unzip_shouldThrowIllegalArgumentException_whenByteArrayToDecompressIsNull() {
        GzipUtil.unzip(null);
    }

    @Test(expected = IllegalArgumentException.class)
    public void unzip_shouldThrowIllegalArgumentException_whenByteArrayToDecompressIsEmpty() {
        GzipUtil.unzip(new byte[0]);
    }

    @Test
    public void unzip_shouldReturnInputByteArrayAsString_whenByteArrayContentIsNotGzipped() {
        String xml = "<Hello>World</Hello>";
        byte[] bytes = xml.getBytes();

        String actual = GzipUtil.unzip(bytes);

        assertEquals(xml, actual);
    }

    @Test
    public void unzip_shouldDecompressByteArrayGzippedContent() throws Exception {
        String xml = "<Hello>World</Hello>";
        byte[] compressed = GzipUtil.zip(xml);

        String actual = GzipUtil.unzip(compressed);

        assertEquals(xml, actual);
    }

    @Test
    public void isZipped_shouldReturnFalse_whenContentIsNotGzipped() {
        byte[] bytes = new byte[] {1,2,3};

        boolean actual = GzipUtil.isZipped(bytes);

        assertFalse(actual);
    }

    @Test
    public void isZipped_shouldReturnTrue_whenContentIsGzipped() {
        byte[] bytes = GzipUtil.zip("1,2,3");

        boolean actual = GzipUtil.isZipped(bytes);

        assertTrue(actual);
    }
}

I hope you find this useful!

Create a free website or blog at WordPress.com.