MERGE Stored Procedures in SQL Server

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 purpose, I created three different MERGE procedures:

Persons Table

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

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:

This is the MergePerson procedure:

This is the script to test the procedure:

This is the result:

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:

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 Persons table. It will have similar behaviour 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:

Let’s now test this procedure:

This is the result:

This is what happened during the execution of the MergePersonsUpsert procedure:

Merge Stored Procedure for Upsert and Delete a list of Persons

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 was 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 Persons 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:

[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 do not exist in the list of persons that was sent to the procedure. In this case, before the DELETE operation, it’s possible to include the “AND target.Id <> 1”:

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:

This is 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.

This is the link for the repository with the scripts in GitHub:

https://github.com/henriquesd/SQLExamples

If you like this solution, I kindly ask you to give an ⭐️ in the repository.

Thanks for reading!

--

--

.NET Software Engineer

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store