Introduction

The MERGE statement can be used to insert, update, or delete data using the same transaction and avoid the need to create separate scripts for each operation. In this article, I present how to use the MERGE statement in a stored procedure to merge a single record and how to use it to merge a list of records by using Table-Valued parameters in SQL Server.

“Merge runs insert, update, or delete operations on a target table from the results of a join with a source table. For example, synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.” (Microsoft Docs)

For demonstration purposes, I created three different MERGE procedures:

Person Table

For the next examples, I created a table of Persons, which contains an Id, Name, LastName, CreatedDate, and ModifiedDate columns:

SQL
CREATE TABLE Persons (
	Id INT NOT NULL,
	Name NVARCHAR(100) NULL,
	LastName NVARCHAR(255) NULL,
	CreatedDate DATETIME2 NOT NULL CONSTRAINT [DF_Persons_CreatedDate] DEFAULT GETUTCDATE(),
	ModifiedDate DATETIME2 NULL
	CONSTRAINT PK_Persons PRIMARY KEY(Id)
 );

This table will be used by the stored procedures that will be created next.

Merge Stored Procedure for upsert a single record

This first procedure will have as parameters an Id, a Name, and a LastName. Then, it will execute the MERGE statement, on which it will upsert a single record into the Persons table. It will have the following behavior:

MergePerson procedure

SQL
CREATE PROCEDURE dbo.MergePerson (  
		@Id INT,
		@Name NVARCHAR(100),
		@LastName NVARCHAR(255)
	)  
AS   
BEGIN

	MERGE Persons as target
		USING (
				SELECT
					@Id as Id,
					@Name as Name,
					@LastName as LastName
			) AS source
			ON target.Id = source.Id

	WHEN NOT MATCHED BY target
		THEN
			INSERT (
				Id,
				Name,
				LastName
			) 
			VALUES
			(
				@Id,
				@Name,
				@LastName
			)
	
	WHEN MATCHED
		THEN  
			UPDATE SET  
				Name = @Name,
				LastName = @LastName,
				ModifiedDate = GETUTCDATE()
	;

END

Script to test the procedure

SQL
BEGIN TRANSACTION

	SELECT * FROM Persons;

	EXEC MergePerson @Id = 1, @Name = 'Frodo', @LastName = 'Baggins';
	EXEC MergePerson @Id = 2, @Name = 'Gandalf', @LastName = 'The Grey';
	EXEC MergePerson @Id = 3, @Name = 'Samwise', @LastName = 'Gamgee';

	SELECT * FROM Persons;

	WAITFOR DELAY '00:00:05';

	EXEC MergePerson @Id = 2 , @Name = 'Gandalf', @LastName = 'The White';

	SELECT * FROM Persons;

ROLLBACK

This is the result

Upsert a single record

Merge Stored Procedure for Upsert a list of Persons

Now think of a scenario where instead of upserting a single record, you will receive a list of persons, and the procedure should take care of multiple records instead of a single one.

In order to receive a list of persons as a parameter, we need to have a Table-Valued parameter. For that, we need to create a User-Defined Table Type (if you are not familiar with Table-Valued parameters, I recommend reading a previous article I wrote about this topic; you can read it by clicking here). This is the User-Defined Type Table that will be used as a parameter to the procedure:

C#
CREATE TYPE PersonType AS TABLE (
	Id INT NOT NULL,
	Name NVARCHAR(100) NULL,
	LastName NVARCHAR(255) NULL,
	PRIMARY KEY(Id)
);

This procedure will have the following behavior: it will receive as a parameter a variable with the type PersonType(which is something similar to a list of persons), then it will execute the MERGE statement, on which it will upsert the records into the Person table. It will have a similar behavior as the previous procedure, but now, instead of considering a single record, it will consider a list of records. This is the procedure behavior:

This is the MergePersonsUpsert procedure

SQL
CREATE PROCEDURE dbo.MergePersonsUpsert (
		@Persons PersonType READONLY
	)
AS   
BEGIN

	MERGE Persons as target
		USING (
			SELECT
				Id,
				Name,
				LastName
			FROM @Persons
			) AS source
		ON target.Id = source.Id

	WHEN NOT MATCHED THEN
	INSERT (
		Id,
		Name,
		LastName
	) 
	VALUES
	(
		source.Id,
		source.Name,
		source.LastName
	)
	
	WHEN MATCHED THEN  
		UPDATE SET  
			Name = source.Name,
			LastName = source.LastName,
			ModifiedDate = GETUTCDATE()
	;  

END

Let’s now test this procedure

C#
BEGIN TRANSACTION

	SELECT * FROM Persons;

	INSERT INTO Persons (Id, Name, LastName)
	VALUES (1, 'Frodo', 'Baggins'),
		   (2, 'Gandalf', 'The Grey'),
		   (3, 'Samwise', 'Gamgee');

	SELECT * FROM Persons;

	WAITFOR DELAY '00:00:05';

	DECLARE @Persons PersonType;

	INSERT INTO @Persons
	SELECT 4, 'Eddard ', 'Stark'
	UNION ALL
	SELECT 2, 'Gandalf', 'The White'
	UNION ALL
	SELECT 5, 'Daenerys', 'Targaryen';

	EXEC MergePersonsUpsert @Persons;

	SELECT * FROM Persons;

ROLLBACK

This is the result

Upsert a list of Persons

What happened during the execution of the MergePersonsUpsert procedure

Merge Stored Procedure for Upsert and Delete a list of Person

Now, let’s include a new requirement for the new procedure: every time the procedure is executed, it’s not only necessary to upsert the records but also delete the records that are in the database but are not in the list of persons that were received as a parameter.

Think of a scenario where your application receives some data from some other application, and you need to override all the data in your database every time the procedure is executed. Perhaps you are thinking that instead of implementing a merge for that, we could only delete all the records and insert them again, this would work of course, but the indexes of the table will be messed up, so to avoid this situation, let’s use the merge statement.

The procedure will receive as a parameter a variable of type PersonType(think of that as something similar to a list of persons), then it will execute the MERGE statement, on which it will upsert or delete records into the Person table. It will have the following behavior:

For this example, I’m going to make use of a temporary table inside the procedure. This is not mandatory, but in case you know that you will receive a big amount of data, this is something that you should consider using because it will improve the performance of the procedure.

This is the MergePersonsWithDelete procedure

SQL
CREATE PROCEDURE dbo.MergePersonsWithDelete (  
		@Persons PersonType READONLY
	)  
AS   
BEGIN
	 
	 IF OBJECT_ID('#PersonsTemp') IS NOT NULL  
		DROP TABLE #PersonsTemp
  
	 CREATE TABLE #PersonsTemp
	 (  
		Id INT NOT NULL,
		Name NVARCHAR(100) NULL,
		LastName NVARCHAR(255) NULL,
		PRIMARY KEY (Id)
	 );
  
	INSERT INTO #PersonsTemp
	SELECT *  
	FROM @Persons  

	MERGE Persons as target
		USING (
			SELECT
				Id,
				Name,
				LastName
			FROM #PersonsTemp
			) AS source
		ON target.Id = source.Id

	WHEN NOT MATCHED THEN
	INSERT (
		Id,
		Name,
		LastName
	) 
	VALUES
	(
		source.Id,
		source.Name,
		source.LastName
	)
	
	WHEN MATCHED THEN  
		UPDATE SET  
			Name = source.Name,
			LastName = source.LastName,
			ModifiedDate = GETUTCDATE()
  
	WHEN NOT MATCHED BY SOURCE   
	THEN   
		DELETE  
	;  

END

[Extra] It’s also possible to include conditions in the merge statement. For instance, using the example of this procedure, let’s say that we never want to delete the record with the primary key 1, even when this record exists in the database but does not exist in the list of persons that were sent to the procedure. In this case, before the DELETE operation, it’s possible to include the “AND target.Id <> 1”:

SQL
WHEN NOT MATCHED BY SOURCE AND target.Id <> 1
 THEN   
  DELETE  
 ;

This means that when target do not match the source AND the target Id is different than 1, then it will delete the record, otherwise will not delete it. We will not use this in this procedure since we want to delete everything, but it’s good to know that it’s possible to do it when necessary.

Let’s now test this procedure

This is the result

Delete a list of Persons

What happened during the execution of the MergePersonsWithDelete procedure?

Conclusion

The MERGE statement is useful when you need to have a procedure that can handle insert and update (upsert) or delete operations in the same transaction without creating separate procedures for each operation. It’s possible to use MERGE for merging a single record, or for merging a list of records.