Select Page

Here’s a simple example of how to use MERGE. I’ll do a follow up post on this later.

SET NOCOUNT ON;
/* TARGET TABLE */
DECLARE @Movies TABLE (
	id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
	,Title VARCHAR(20)
	,Qty INT NOT NULL
	,CreateDate DATETIME DEFAULT(GETDATE())
	,ModifiedDate DATETIME
);
/* SOURCE TABLE */
DECLARE @AvailableMovies TABLE (
	id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
	,Title VARCHAR(20)
	,Qty INT NOT NULL
	,CreateDate DATETIME DEFAULT(GETDATE())
);

/* TARGET RECORDS */
INSERT INTO @Movies (Title, Qty) VALUES ('Thomas Crown Affair',3);
INSERT INTO @Movies (Title, Qty) VALUES ('Breakfast Club',2);
INSERT INTO @Movies (Title, Qty) VALUES ('Iron Man',5);
INSERT INTO @Movies (Title, Qty) VALUES ('Avengers',14);
INSERT INTO @Movies (Title, Qty) VALUES ('Thor',1);
INSERT INTO @Movies (Title, Qty) VALUES ('Seven Years in Tibet',1);

/* SOURCE RECORDS */
INSERT INTO @AvailableMovies (Title, Qty) VALUES ('Thomas Crown Affair',1);
INSERT INTO @AvailableMovies (Title, Qty) VALUES ('Breakfast Club',2);
INSERT INTO @AvailableMovies (Title, Qty) VALUES ('Iron Man',0);
INSERT INTO @AvailableMovies (Title, Qty) VALUES ('Avengers',4);
INSERT INTO @AvailableMovies (Title, Qty) VALUES ('Thor',2);
INSERT INTO @AvailableMovies (Title, Qty) VALUES ('The Last Samurai',2);
SET NOCOUNT OFF;

/* BEFORE LOOK */
SELECT * FROM @Movies;
SELECT * FROM @AvailableMovies;

/* PERFORM MERGE */
MERGE @Movies AS m
USING @AvailableMovies AS a 
ON (m.Title = a.Title)
WHEN MATCHED 
	/* UPDATE QTY ON TARGET USING SOURCE QTY */
	THEN UPDATE SET m.Qty = a.Qty, m.ModifiedDate = GETDATE()
WHEN NOT MATCHED BY TARGET
	/* INSERT RECORD TO TARGET THAT IS ONLY IN SOURCE */
	THEN INSERT(Title,Qty) VALUES (a.Title,a.Qty)
WHEN NOT MATCHED BY SOURCE
	/* DELETE RECORD FROM TARGET THAT IS NOT IN SOURCE */
	THEN DELETE
OUTPUT $action, inserted.*, deleted.*;

/* AFTER LOOK */
SELECT * FROM @Movies;
SELECT * FROM @AvailableMovies;

MERGE

Share This