angrysql

What is MERGE?

MERGE is a powerful keyword in Microsoft Transact-SQL (aka 'T-SQL') that is used to perform an UPDATE and INSERT on the same table at the same time. NOTE: MERGE only works with Microsoft SQL Server 2008 and above.

Consider the following scenario: You have a Users table in your database:

CREATE TABLE Users ( Username VARCHAR(32) NOT NULL, Email VARCHAR(128) NOT NULL, NameFirst VARCHAR(32) NOT NULL, NameLast VARCHAR(32) NOT NULL, FavoriteColor VARCHAR(16) NOT NULL, DateCreated DATETIME NOT NULL, DateModified DATETIME NOT NULL )

You need to update your Users table with all the latest user information, so HR gives you a CSV file with all the data. You have a few options at this point:

TRUNCATE your Users table and just insert all the data as it appears in the CSV

Import the data to a holding table and perform some IF EXISTS... THEN UPDATE... ELSE INSERT... magic that would split the process into several commands

Import the data to a holding table and use a MERGE to update and insert at the same time

MERGE is a cleaner, more readable solution - let's take a look at what a MERGE in this situation would look like...

What does it look like?

Let's assume that we have imported our CSV to a work table called UsersUpdate:

CREATE TABLE UsersUpdate ( UpdateUsername VARCHAR(32) NOT NULL, UpdateEmail VARCHAR(128) NOT NULL, UpdateNameFirst VARCHAR(32) NOT NULL, UpdateNameLast VARCHAR(32) NOT NULL, UpdateFavoriteColor VARCHAR(32) NOT NULL )

We are going to assume that our identity columns from Users and UpdateUsers are Username and UpdateUsername, respectively - this means that if we have a match between Users.Username and UpdateUsers.UpdateUsername, that we are going to update the existing record in Users - and if not, that we are going to create a new record in Users.

So, to recap -

All current user data is in UpdateUsers

We are going to match the records on Users.Username and UpdateUsers.UpdateUsername

If we have a match, we are going to update the existing record in Users - and if not, we are going to create a new record in Users

Here's our MERGE statement - I'm going to show it in its entirety here, then we'll break it down line by line:

MERGE Users AS [target] USING ( SELECT UpdateUsername, UpdateEmail, UpdateNameFirst, UpdateNameLast, UpdateFavoriteColor FROM UsersUpdate ) AS [source] ON [source].UpdateUsername = [target].Username WHEN MATCHED THEN UPDATE SET [target].Email = [source].UpdateEmail, [target].NameFirst = [source].UpdateNameFirst, [target].NameLast = [source].UpdateNameLast, [target].FavoriteColor = [source].UpdateFavoriteColor, [target].DateModified = GETDATE() WHEN NOT MATCHED THEN INSERT ( Username, Email, NameFirst, NameLast, FavoriteColor, DateCreated, DateModified ) VALUES ( [source].UpdateUsername, [source].UpdateEmail, [source].UpdateNameFirst, [source].UpdateNameLast, [source].UpdateFavoriteColor, GETDATE(), GETDATE() );

So, from the top:

MERGE Users AS [target]

The first line of the MERGE statement is used to set up the target table for the merge - this is the table that your inserted or updated records are going to be written to.

USING ( SELECT UpdateUsername, UpdateEmail, UpdateNameFirst, UpdateNameLast, UpdateFavoriteColor FROM #UsersUpdate ) AS [source]

The USING block is where you set up your source information - i.e., the table or derived table that holds the data that you want to update your target table with. You can put pretty much anything in here - UNIONs, temp tables, derived tables - you just have to make sure to have column names for your data.

ON [source].UpdateUsername = [target].Username

This is where you specify the columns that you want to match between the source and the target. Think of the MERGE...USING... block like a JOIN.

The rest of the query is pretty self-explanatory:

WHEN MATCHED THEN UPDATE SET [target].Email = [source].UpdateEmail, [target].NameFirst = [source].UpdateNameFirst, [target].NameLast = [source].UpdateNameLast, [target].FavoriteColor = [source].UpdateFavoriteColor, [target].DateModified = GETDATE() WHEN NOT MATCHED THEN INSERT ( Username, Email, NameFirst, NameLast, FavoriteColor, DateCreated, DateModified ) VALUES ( [source].UpdateUsername, [source].UpdateEmail, [source].UpdateNameFirst, [source].UpdateNameLast, [source].UpdateFavoriteColor, GETDATE(), GETDATE() );

When the values in the ON are matched, the target gets updated with the data from the source as specified; when the values don't match, an INSERT is performed to the target and a new record is created.

One last thing to take note of:

);

MERGE statements must always end in a semi-colon.

But I'm one of those people that needs hands-on experience to understand anything!

So am I. SQL is a scripting language and it has temp tables, which means that with enough effort, virtually any concept can be demonstrated in a script that anyone should be able to copy and paste into SQL Server Management Studio. That said, here's a demo of MERGE that uses temp versions of the tables I described earlier:

-- setup IF OBJECT_ID('tempdb..#Users') IS NOT NULL DROP TABLE #Users CREATE TABLE #Users ( Username VARCHAR(32) NOT NULL, Email VARCHAR(128) NOT NULL, NameFirst VARCHAR(32) NOT NULL, NameLast VARCHAR(32) NOT NULL, FavoriteColor VARCHAR(16) NOT NULL, DateCreated DATETIME NOT NULL, DateModified DATETIME NOT NULL ) INSERT INTO #Users ( Username, Email, NameFirst, NameLast, FavoriteColor, DateCreated, DateModified ) VALUES ('jmpavlick', '[email protected]', 'John', 'Pavlick', 'gray', '2012-01-01', '2013-12-22'), ('ksmith', '[email protected]', 'Karen', 'Smith', 'sepia', '2013-09-28', '2014-01-02'), ('tward', '[email protected]', 'Taylor', 'Ward', 'blue', '2012-08-31', '2012-08-31') -- let's look at the data in our #Users table SELECT * FROM #Users -- now let's create a #UsersUpdate table with data that we want to MERGE int our #Users table IF OBJECT_ID('tempdb..#UsersUpdate') IS NOT NULL DROP TABLE #UsersUpdate CREATE TABLE #UsersUpdate ( UpdateUsername VARCHAR(32) NOT NULL, UpdateEmail VARCHAR(128) NOT NULL, UpdateNameFirst VARCHAR(32) NOT NULL, UpdateNameLast VARCHAR(32) NOT NULL, UpdateFavoriteColor VARCHAR(32) NOT NULL ) INSERT INTO #UsersUpdate ( UpdateUsername, UpdateEmail, UpdateNameFirst, UpdateNameLast, UpdateFavoriteColor ) VALUES ('rsommer', '[email protected]', 'Robert', 'Sommer', 'red'), ('ksmith', '[email protected]', 'Karen', 'Smith', 'purple'), ('jdavis', '[email protected]', 'Joe', 'Davis', 'silver'), ('tward', '[email protected]', 'Taylor', 'Ward', 'pink') -- let's look at the data in our #UsersUpdate table SELECT * FROM #UsersUpdate -- now that all the setup is done, let's get on with the MERGE -- we want to create new records or update existing records in #Users -- from the data in #UsersUpdate MERGE #Users AS [target] USING ( SELECT UpdateUsername, UpdateEmail, UpdateNameFirst, UpdateNameLast, UpdateFavoriteColor FROM #UsersUpdate ) AS [source] ON [source].UpdateUsername = [target].Username WHEN MATCHED THEN UPDATE SET [target].Email = [source].UpdateEmail, [target].NameFirst = [source].UpdateNameFirst, [target].NameLast = [source].UpdateNameLast, [target].FavoriteColor = [source].UpdateFavoriteColor, [target].DateModified = GETDATE() WHEN NOT MATCHED THEN INSERT ( Username, Email, NameFirst, NameLast, FavoriteColor, DateCreated, DateModified ) VALUES ( [source].UpdateUsername, [source].UpdateEmail, [source].UpdateNameFirst, [source].UpdateNameLast, [source].UpdateFavoriteColor, GETDATE(), GETDATE() ); -- now that our data is merged, select from #Users to see the result! SELECT * FROM #Users

Whenever I'm creating a new database, my tables, procedures, constraints, and other objects usually undergo significant refactoring as part of the development process. I've found that a good pattern to use for maintaining database objects - especially tables where lots of keys are used - looks something like this:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SomeTableName') AND TYPE IN (N'U')) DROP TABLE dbo.SomeTableName CREATE TABLE dbo.SomeTableName ( -- table definition here )

When I create a new object, I make sure to drop it if it exists, then I re-create it. This way, I can run my script over and over and over and every time, it will put the database into a known state with all the objects I need.

This works great, but what if I'm refactoring and I end up eliminating a table from my schema? If I don't remember to stop and DROP it right then, it's not going to be DROPped when I re-run my script, because when I remove the code to CREATE the table, I remove the code to DROP it as well.

So, in effect, I end up with these rogue tables with no key relationships to anything that remain undetected until several months later, when I'm working on something and they accidentally show up as Intellisense suggestions. "Did I delete that? I don't remember if I deleted that or not... why is this still here?"

As it turns out, there's a really easy way to query sys.objects to see if there are any user-created objects in the current database:

SELECT * FROM sys.objects WHERE is_ms_shipped = 0

That's it. is_ms_shipped is just a BIT column in sys.objects - if it's set to 0, that just means it's not a built-in object.

select CAST(CAST(YEAR(GETDATE()) AS VARCHAR(4)) + '-' + SUBSTRING(CAST(DATEPART(mm, GETDATE())+100 AS VARCHAR(3)), 2, 2) + '-' + SUBSTRING(CAST (DATEPART(dd, GETDATE())+100 AS VARCHAR(3)), 2, 2) AS VARCHAR(10))