Merge–under used statement

Published 07 May 11 04:54 PM | MartinCroft

 

Day 7. The MERGE statement is one that quite a few DBA are aware of and use but is under used by developers. I think because they are not aware of all the scenarios that it can be used in. I recently had a variety of scenarios that developers had asked my opinion on , or a review of some code and found its perfect to use MERGE. I put together some examples that show all the different things that can be done with MERGE from the obvious to the more specialist situations.

This example uses AdventureWorks and also shows some other lesser used bits of T-SQL. The full script can be found here

The code to generate the example tables

--=====================================================================================
--Populate dummy data for example using CROSS JOIN to generate multiple rows
-- and Row_number to create unique customer numbers 
--=====================================================================================
INSERT INTO CustomersCache (Name,City,AnnualEarnings) 
SELECT 
	Name + ' ' + CAST(ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Name )AS VARCHAR(3)) ,City,10000
FROM
(
SELECT TOP 10
      [Name]
 FROM 
	[AdventureWorks].[Sales].[Store] 
)a
CROSS JOIN  
(
SELECT DISTINCT TOP 30 
	[City]
FROM 
	[AdventureWorks].[Person].[Address]
)b 

 

populate script using a cross join to simplify creating dummy data. Its very easy to populate lots of rows using a cross join, but I wanted them to be distinct so used

    Name + ' ' + CAST(ROW_NUMBER() OVER(PARTITION BY Name ORDER BY Name )AS VARCHAR(3))

to give me a unique row

unique_rows

 

So we have two tables

CustomerCache and Customers. The cache table is populated and the customer table is empty.

running the following basic MERGE statement

--=====================================================================================
--Simple MERGE statement any rows that dont exist in 
--TARGET table will be populated 
--=====================================================================================
MERGE Customers AS TARGET
USING CustomersCache AS SOURCE  ON(TARGET.Name = SOURCE.NAME)
WHEN NOT MATCHED THEN
INSERT (Name, City, [AnnualEarnings], ModifiedDate)
VALUES (Name, City, [AnnualEarnings], ModifiedDate);
	

The trigger shows we have inserts occurring and checking both tables we would find they are now in sync.

Ok lets simulate a scenario where customer accounts no longer exist the easiest approach is to delete some rows out of the cache table

 

DELETE TOP(5) FROM CustomersCache
OUTPUT deleted.*

 

Using DELETE  TOP(5) we can just delete 5 rows without the need of a WHERE clause and using OUTPUT ( another under rated and used T-SQL function, I’ll blog about this in the next couple of weeks) we can see the rows its deleted.

deletedrows

ok lets show how to deactivate the customers

MERGE Customers AS TARGET
USING CustomersCache AS SOURCE  ON(TARGET.Name = SOURCE.NAME)
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET Active =0, ModifiedDate =GETDATE();

So any rows that don’t match between source and destination are now set de-activated and the modified date updated. (shown below). The trigger shows updates occur

deactiverows

Ok, now lets delete another row and also modify some data. This time i’ll show how you can delete a random row using ORDER BY newiD() and OUTPUT again, notice the placement of the OUTPUT keyword, it comes after the DELETE statement and not at the end of the query.

--Delete a Random row from Source 
DELETE TOP(1) CustomersCache
OUTPUT deleted.*
WHERE Name = (SELECT TOP 1 Name from CustomersCache ORDER BY newiD())

And also add in a new company

 

--Add New Company
INSERT INTO CustomersCache
SELECT  TOP 1
	'Martins New Company 1',
	CITY,
	AnnualEarnings,
	GETDATE() 
FROM 
	CustomersCache
--ok lets amend some values as well. Random of course.
--The code below gives a random value between 1 and 1000,
-- also using the 2008 syntax += instead of a=a+1 can put a+=1
 
 UPDATE 	TOP(1) CC
SET AnnualEarnings+= (1+ CAST(1000*RAND()AS INT)),
    ModifiedDate =GETDATE()
OUTPUT 
    deleted.Name,
    deleted.AnnualEarnings As Before,
    inserted.AnnualEarnings AS After
FROM CustomersCache CC
WHERE ModifiedDate IS NULL 

Ok that just updates 1 row, before anyone pulls me I said add some values. If you add

G0
50

at the end of the code and run it all, you get the code to ran 50 times, this is a very useful bit of code for generating lots of rows quickly, or dropping into someone code at the bottom of a script if they have left their machine unlocked ", only works in Management studio ! as its the batch separator

SELECT “I am an IDIOT”

GO 5000

you get the idea, not saying I would use the word idiot necessarily!.

* Warning if you highlight a lot of statements and add a GO 50 it will execute THEM ALL 50 times, that why in my full script you wont find the GO 10, I add it when demonstrating each section *

Right lets MERGE them into the Customers table ( Sorry couldn’t resist). I am going to use OUTPUT now with the MERGE statement so two cool functions together. However, you can’t use OUTPUT and MERGE if you have triggers on the table so I drop the triggers first.

He’s the code that will Insert new rows, update and amendments and update the modified date and de-activate the rows that no longer exist.

MERGE Customers AS TARGET
USING CustomersCache AS SOURCE  ON(TARGET.Name = SOURCE.NAME)
WHEN NOT MATCHED THEN
INSERT (Name, City, [AnnualEarnings], ModifiedDate)
VALUES (Name, City, [AnnualEarnings], ModifiedDate)
WHEN MATCHED AND
(
	TARGET.[AnnualEarnings] <> SOURCE.[AnnualEarnings]
)
THEN
UPDATE
SET TARGET.[AnnualEarnings]=SOURCE.[AnnualEarnings],
TARGET.ModifiedDate =GETDATE()
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET Active =0, ModifiedDate =GETDATE()
OUTPUT $Action AS Action,
	   Inserted.Custid,
	   Inserted.Name,
	   Deleted.ModifiedDate,
	   Deleted.AnnualEarnings,
	   Inserted.AnnualEarnings;

With the OUTPUT and MERGE statement you can use $Action keyword that will allow you see see the changes. Which can be very useful if you want to do another action dependant on what occurred and UPDATE or a DELETE.  I did use this mechanism for a complex next customer / policy number scenario based on two table which I’ll look to blog about over the coming weeks.

which outputs (umm) the following

outputAction

Conditional MERGE

Ok, lets say we are interested in Auditing our table to make sure any changes we get to keep a history of, only updates not interested in deletes.

We create an Auditing table and update 5 rows ( remember the warning) when this works running the entire script and that is because a GO precedes the statement we want to run 5 times! We are outputting again the $Action value.

CREATE TABLE Auditing
(
	[AuditID] [smallint] IDENTITY(1,1) NOT NULL,
	[Name] VARCHAR(50) NOT NULL,
	[City] VARCHAR(30) NOT NULL,
	[EarningsBefore] INT, 
	[EarningsAfter] INT, 
	[ModifiedDate] [datetime]  NULL,
	[Type] VARCHAR(10),	
	Active BIT DEFAULT (1)
)
	GO
	
	--Update 5 records using GO 
	UPDATE 	TOP(1) CC
	SET AnnualEarnings+= (1+ CAST(1000*RAND()AS INT)),
		ModifiedDate =GETDATE()
	OUTPUT deleted.Name,deleted.AnnualEarnings As Before,inserted.AnnualEarnings AS After
	FROM CustomersCache CC
	WHERE ModifiedDate IS NULL
	GO 5
	
	INSERT INTO Auditing(Name,City,[EarningsBefore],[EarningsAfter],ModifiedDate, Type)
	SELECT Name,City,EarningsBefore,EarningsAfter,ModifiedDate, Action
	FROM
	(
	MERGE Customers AS TARGET
	USING CustomersCache AS SOURCE  ON(TARGET.Name = SOURCE.NAME)
	
	WHEN NOT MATCHED THEN
	INSERT (Name, City, [AnnualEarnings], ModifiedDate)
	VALUES (Name, City, [AnnualEarnings], ModifiedDate)
	
	WHEN MATCHED AND
	(
		TARGET.[AnnualEarnings] <> SOURCE.[AnnualEarnings]
	)
	THEN
	UPDATE
	SET TARGET.[AnnualEarnings]=SOURCE.[AnnualEarnings],
	TARGET.ModifiedDate =GETDATE()
	--Output chanegs to Auditing Table showing before and after picture 
	OUTPUT $Action AS Action,
		   Inserted.Name,
		   Inserted.City,
		   Deleted.AnnualEarnings AS EarningsBefore,
		   Inserted.AnnualEarnings AS EarningsAfter ,
		   Deleted.ModifiedDate
		   
) AS D
WHERE Action = 'UPDATE';

and  Inserting into the Auditing table where the action is an update. The Target table is still getting update but we are capturing all the updates that occur.

Filed under: , , ,

Comments

# Using Output | All Things SQL said on May 4, 2014 02:52 PM:

Pingback from  Using Output | All Things SQL