Using MERGE in SQL Server to do an INSERT or UPDATE on a Table

By Steven McLintock on March 31, 2021

I’ve never really made much use of the MERGE statement in SQL Server, but the other day I wanted to do either an INSERT or an UPDATE on a table, depending on if there’s a match on a particular column.

For example, if I had the table ‘Contacts’ containing names, addresses, phone numbers and email addresses, I don’t want the table to include duplicate email addresses:

Table in SQL Server

If I try to add a new contact to the table, the MERGE statement in SQL Server will either insert a new row, or alternatively update an existing row that contains the same email address:

MERGE [dbo].[Contacts] AS TARGET
USING
	(VALUES (@EmailAddress))
	AS SOURCE (EmailAddress)
ON
	TARGET.EmailAddress = SOURCE.EmailAddress
WHEN NOT MATCHED BY TARGET THEN
INSERT (
	[FirstName],
	[LastName],
	[Address1],
	[Address2],
	[City],
	[Province],
	[PostalCode],
	[EmailAddress],
	[PhoneNumber]
) VALUES (
	@FirstName,
	@LastName,
	@Address1,
	@Address2,
	@City,
	@Province,
	@PostalCode,
	@EmailAddress,
	@PhoneNumber
)
WHEN MATCHED THEN 
UPDATE SET 
    [FirstName] = @FirstName,
    [LastName] = @LastName,
    [Address1] = @Address1,
    [Address2] = @Address2,
    [City] = @City,
    [Province] = @Province,
    [PostalCode] = @PostalCode,
    [PhoneNumber] = @PhoneNumber;

If I need to use SCOPE_IDENTITY(), I can do the following to either retrieve the identity of the inserted row if it’s an INSERT, or the identity of the affected row if it’s an UPDATE:

DECLARE @ContactId INT

MERGE [dbo].[Contacts] AS TARGET
USING
	(VALUES (@EmailAddress))
	AS SOURCE (EmailAddress)
ON
	TARGET.EmailAddress = SOURCE.EmailAddress
WHEN NOT MATCHED BY TARGET THEN
INSERT (
	[FirstName],
	[LastName],
	[Address1],
	[Address2],
	[City],
	[Province],
	[PostalCode],
	[EmailAddress],
	[PhoneNumber]
) VALUES (
	@FirstName,
	@LastName,
	@Address1,
	@Address2,
	@City,
	@Province,
	@PostalCode,
	@EmailAddress,
	@PhoneNumber
)
WHEN MATCHED THEN 
UPDATE SET 
    [FirstName] = @FirstName,
    [LastName] = @LastName,
    [Address1] = @Address1,
    [Address2] = @Address2,
    [City] = @City,
    [Province] = @Province,
    [PostalCode] = @PostalCode,
    [PhoneNumber] = @PhoneNumber,
    @ContactId = TARGET.[ContactId];

SELECT ISNULL(@ContactId, SCOPE_IDENTITY())

More posts like this...

Auto-Generate Insert Statements With Data Using SQL Server Management Studio

By Steven McLintock on April 30, 2021

If you are working with a database in SQL Server Management Studio (SSMS), you may find you need a way to auto-generate INSERT statements with the data from a table. This might be for inserting the data into a different database for testing purposes, or simply as a backup.