What the heck? Even indexes have WHERE clauses these days. I can’t remember what I was reading when I saw this but it completely flabbergasted me. If you go look at MERGE in BOL you will see what I mean. So does this mean you can’t restrict data in a MERGE?

No, of course not. There are in fact several different ways I know of off-hand that you can handle it. You can use subqueries and CTEs to modify your source or you can add search conditions to your WHEN MATCHED and WHEN NOT MATCHED clauses.

Here is an example.

Adventureworks is having a major reorg. First we set up the update table (note this is just a sample of the extensive changes they are making).

USE AdventureWorks2008;
GO
SELECT TOP 0 CAST(NULL AS smallint) AS DepartmentID, Name, GroupName
INTO dbo.DepartmentReorg 
FROM HumanResources.Department;
GO
INSERT INTO dbo.DepartmentReorg VALUES (10, 'Accounting', 'Executive General and Administration');
INSERT INTO dbo.DepartmentReorg VALUES (11, 'Information Technology', 'Executive General and Administration');
INSERT INTO dbo.DepartmentReorg VALUES (12, 'Document Control', 'Executive General and Administration');
INSERT INTO dbo.DepartmentReorg VALUES (13, 'Quality Assurance', 'Executive General and Administration');
INSERT INTO dbo.DepartmentReorg VALUES (NULL, 'Foreign Sales', 'Sales and Marketing');
GO

Now the MERGE statement

MERGE INTO HumanResources.Department Dep
USING DepartmentReorg Reorg
ON Dep.DepartmentID = Reorg.DepartmentID
WHEN MATCHED THEN
UPDATE SET Name = Reorg.Name, GroupName = Reorg.GroupName, ModifiedDate = GetDate()
WHEN NOT MATCHED BY TARGET THEN
INSERT (Name, GroupName, ModifiedDate) VALUES (Reorg.Name, Reorg.GroupName, GetDate());

But wait, management has decided they aren’t ready for the whole reorg yet. They want just the changes to the “Executive General and Administration” group. They don’t want us to get rid of the reorg table since it took weeks to set up correctly, but they do want only the appropriate changes made. So here an example of possible changes to the MERGE command using search commands.

MERGE INTO HumanResources.Department Dep
USING DepartmentReorg Reorg
ON Dep.DepartmentID = Reorg.DepartmentID
WHEN MATCHED AND Reorg.GroupName = 'Executive General and Administration' THEN
UPDATE SET Name = Reorg.Name, GroupName = Reorg.GroupName, ModifiedDate = GetDate()
WHEN NOT MATCHED BY TARGET AND Reorg.GroupName = 'Executive General and Administration' THEN
INSERT (Name, GroupName, ModifiedDate) VALUES (Reorg.Name, Reorg.GroupName, GetDate());

Of course that requires putting the condition in two places, and if it changes it has to be modified in two places. Although this does give you more fine control if you need it, in our case it makes it easier for mistakes to happen. So here is an example of putting the condition in one place using a subquery.

MERGE INTO HumanResources.Department Dep
USING (SELECT *
FROM DepartmentReorg 
WHERE GroupName = 'Executive General and Administration') AS Reorg
ON Dep.DepartmentID = Reorg.DepartmentID
WHEN MATCHED THEN
UPDATE SET Name = Reorg.Name, GroupName = Reorg.GroupName, ModifiedDate = GetDate()
WHEN NOT MATCHED BY TARGET THEN
INSERT (Name, GroupName, ModifiedDate) VALUES (Reorg.Name, Reorg.GroupName, GetDate());

Still not my favorite since I find in line queries to be somewhat messy and make the code a bit harder to read. So here is an example using a CTE.

WITH Reorg AS (SELECT *
FROM DepartmentReorg 
WHERE GroupName = 'Executive General and Administration')
MERGE INTO HumanResources.Department Dep
USING Reorg
ON Dep.DepartmentID = Reorg.DepartmentID
WHEN MATCHED THEN
UPDATE SET Name = Reorg.Name, GroupName = Reorg.GroupName, ModifiedDate = GetDate()
WHEN NOT MATCHED BY TARGET THEN
INSERT (Name, GroupName, ModifiedDate) VALUES (Reorg.Name, Reorg.GroupName, GetDate());

Take your pick of course.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, language sql, microsoft sql server, sql statements, T-SQL