Up to this point in our design patterns sessions we’ve concentrated on removing duplicates from our troubled table. Now, let’s look into preventing those duplicates from being inserted in the first place. If you haven’t heard the phrase upsert before, it’s the idea of doing an INSERT and UPDATE in the same statement, that way you handle changing data and inserting it in one go. It’s very helpful for preventing duplicate data from being inserted.
For this article we’re going to use T-SQL to prevent these duplicates, rather than establishing a constraint, and then building an error handler when we try to insert duplicate data. Let’s dig in.
CREATE TABLE [dbo].[TroubledTable]( [FirstName] [varchar](50) NULL, [LastName] [varchar](50) NULL, [email] [varchar](255) NULL )
Consider our simple troubled table. This little table has done a good job of collecting contact information so far, we decide, we have no good reason to change the structure, we just want to make it so if a user wants to enter the same row twice, it won’t actually INSERT into the table. If we use an IF THEN construct, we can check to make sure that row doesn’t already exist. Once we know the record doesn’t exist, we can run the INSERT statement. Here’s what that would look like.
IF NOT EXISTS (
SELECT FirstName
FROM TroubledTable
WHERE
FirstName = <value>
AND LastName = <value>
AND email = <value>
)
INSERT INTO TroubledTable
(FirstName, LastName, email)
VALUES <value>, <value>, <value>
Pretty easy right? Now this solution doesn’t really allow us to update information. If we wanted to allow updates, we could add a primary key to the table, so we can uniquely identify each row, we could change the construct to search for a primary key, and when found UPDATE, otherwise it runs an INSERT. In this new scenario we could have two people with the same name, but no two users are allowed to have the same email address.
First we set up a variable to hold the primary key in the case we want to do an update.
DECLARE @PKToUpdate INT
Next, we’re going to look for a value for @PKToUpdate.
SELECT @PKToUpdate = ttPrimaryKey FROM TroubledTableWithPK WHERE email = <value>
If we find no rows, then @PKToUpdate will be NULL. So we can use that knowledge to build our upsert.
IF (@PKToUpdate IS NOT NULL) UPDATE TroubledTableWithPK SET FirstName = <value> , LastName = <value> , email = <value> WHERE ttPrimaryKey = @PKToUpdate ELSE INSERT INTO TroubledTable (FirstName, LastName, email) VALUES <value>, <value>, <value>
Easy, right? Either of these methods work really well for the single row at a time system, such as a web form built for one user at a time to come in and register. But what if you had a system that allowed bulk inserts?
This is where the MERGE statement shines. This is a much more elegant solution to handling all three operations in a single statement: INSERT, UPDATE, and DELETE. If you haven’t seen this one before, don’t worry about it. It’s all concepts you already know, just put together in a new way! Check it out.
We first have to think about our data as having two tables: a source table and a target.
In our example we’re going to use my OriginalTroubledTable, a backup I made of TroubledTable as my source. It has the same structure as TroubledTable, so it makes things a bit easier. Now that we have a source and a target, we have to decide how we’ll match rows between the two. Just like a JOIN statement. When doing UPSERTS, you’re generally going to want to want the JOIN (or match) criteria to be the information that makes each row unique. In our case we don’t want duplicate email addresses. So we’re going to join on email from each table.
So far we have this much of our MERGE statement written by coming up with the source, target and JOIN criteria.
MERGE INTO TroubledTable AS [target] USING OriginalTroubledTable as [source] ON [target].email = [source].email
Next, we have to decide what we’re going to do when we have a match, when we don’t have a match in the target, and what we want to do if there’s no match in the source. All three of these are things you can handle with the MERGE statement.
So, in the case where we have a match on our join criteria, we will want to do an UPDATE. If we don’t have a match in the target, we’re going to do an INSERT. If we don’t have a match in the source, we’re not going to do anything, so at least that’s easy!
Here’s how we construct that MERGE statement:
MERGE INTO TroubledTable AS [target]
USING OriginalTroubledTable as [source]
ON [target].email = [source].email
WHEN MATCHED THEN
UPDATE SET [target].FirstName = [source].FirstName
, [target].LastName = [source].LastName
WHEN NOT MATCHED BY TARGET THEN
INSERT (FirstName, LastName, email)
VALUES ([source].FirstName, [source].LastName, [source].email);
So when we run this, any records missing from the target are added. Any records that already exist will have their first and last names updated. And we’re all good right?
Msg 8672, Level 16, State 1, Line 1 The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
That’s right, Merge doesn’t handle it if you pass it if your source table has duplicates in it! You might want to check out my de-duping articles.
Either that, or you could use a sub query for the source.
MERGE INTO TroubledTable AS [target] USING ( SELECT DISTINCT FirstName, LastName, email FROM OriginalTroubledTable) as [source] ON [target].email = [source].email --now we're only looking to make emails distinct WHEN MATCHED THEN UPDATE SET [target].FirstName = [source].FirstName , [target].LastName = [source].LastName --no need to include email, since that's our join criterion WHEN NOT MATCHED BY TARGET THEN INSERT (FirstName, LastName, email) VALUES ([source].FirstName, [source].LastName, [source].email);
Learning to read between the lines when you get a SQL error will go a long way in your career! This is just one chance to do that.
These are my two patterns for dealing with upserts, If you have another way, please let me know! I’m always interested in seeing new ways to do things.
As always if you have any questions about this, or anything else in SQL, let me know! I’m here to help.