Category: Custom Software • Tag: • Published 2014-03-23
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.
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
CREATE TABLE Person
PersonId int primary key,
INSERT INTO Person
(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.
DECLARE @PersonSource TABLE
INSERT INTO @PersonSource
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 Person AS T --target
USING @PersonSource AS S --source
ON T.PersonId = S.PersonId --match on
WHEN MATCHED THEN --update
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
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.
- It is not required to include then WHEN/DELETE, in this example you would need to pass in the entire table each time, not a great idea.
- End with Semicolon, End with Semicolon, End with Semicolon!!!
- You can potentially have multiple WHEN/UPDATE, WHEN/INSERT, WHEN/DELETE because you are allowed to add conditions for each. Which would allow you to include business logic here if needed.
- Example: WHEN MATCHED AND T.FirstName <> S.FirstName AND T.LastName <> S.LastName THEN
Feedback? Better Idea? General Comment?