T-SQL: Intro to MERGE
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
Happy MERGEing!