Post Date: 2014-03-24
Database, Database, Database. They are everywhere, and have been for a long time. Personally I find working with a database enjoyable, especially when the requirements get twisted and you need to expand your mind to solve the problem. This blog post is not about one of those mind-bending situations. Just some basics that help with everyday SQL. This one is actually dated now, but still not used as much as it probably should be.
MERGE
Starting back in SQL 2008 you could use the MERGE command to essentially combine your INSERT, UPDATE, DELETE logic into a single statement. This is nice if you still write your SQL code (which I do) and haven't moved to an ORM (note: I'm not a fan), but that is for another post another day. Best way to learn is to see an example:
1) First let's build some junk data
--target table
CREATE TABLE Person
(
PersonId int primary key,
FirstName varchar(64),
LastName varchar(64),
CreateDate datetime,
ModifyDate datetime
)
--populate target
INSERT INTO Person
VALUES
(1, 'FN1','LN1', GETDATE(), GETDATE()),
(2, 'FN2','LN2', GETDATE(), GETDATE()),
(3, 'FN3','LN3', GETDATE(), GETDATE()),
(4, 'FN4','LN4', GETDATE(), GETDATE())
-- current target
SELECT * FROM Person
2) Next we will build a Table Variable that contains the dataset containing 'new' data to be merged into the database.
--source table
DECLARE @PersonSource TABLE
(
PersonId int,
FirstName varchar(64),
LastName varchar(64)
)
--populate source
INSERT INTO @PersonSource
VALUES
(1, 'FNA','LNA'),
(2, 'FNB','LNB'),
(6, 'FN6','LN6'),
(7, 'FN7','LN7')I wanted to use a table variable here because this could potentially be a way you could pass a dataset to an inline sql statement or a stored procedure if needed.
3) Then the Merge statement
--merge
MERGE Person AS T --target
USING @PersonSource AS S --source
ON T.PersonId = S.PersonId --match on
WHEN MATCHED THEN --update
UPDATE SET
T.FirstName = S.FirstName,
T.LastName = S.LastName,
T.ModifyDate = GETDATE()
WHEN NOT MATCHED BY TARGET THEN --insert
INSERT (PersonId, FirstName, LastName, CreateDate, ModifyDate)
VALUES (S.PersonId, S.FirstName, S.LastName, GETDATE(), GETDATE())
WHEN NOT MATCHED BY SOURCE THEN --delete
DELETE;
SELECT * FROM Person
I've added comments on the important sections of a MERGE statement. You can see we alias Person as T (for target) and @PersonSource as S (for source). You can then choose with the ON statement what MATCH means, in this case we are looking for a match on the primary key which is a very common scenario. With the SOURCE, TARGET and MATCH defined, we then move into the WHEN area of the MERGE statement. You can see here that when the primary key is matched we define the update statement on the TARGET. If the primary key is not matched we do an insert. Otherwise if the data in the TARGET is not in the SOURCE we say delete.
Important Notes: