Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

The MERGE statement doesn’t have a WHERE clause.

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

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...