MERGE SQL – MERGE operation in Transact-SQL language

MERGE SQL – In Transact-SQL language in one row with such operations as INSERT (insert), UPDATE (update), DELETE (delete) is the operation MERGE (merge), which in some cases may be useful, but for some reason, some do not know about it and do not use it, so today we will consider this operation and look at examples.

What is MERGE in T-SQL?

MERGE is an operation in T-SQL language that updates, inserts or deletes data in a table based on the results of connection to the data of another table or SQL query. In other words, you can use MERGE to merge two tables, i.e. synchronize them.

In a MERGE operation, a key field or fields of the main table (where all changes will be made) are merged with the corresponding fields of another table or query result. As a result, if the condition under which the merge is performed is true (WHEN MATCHED), we can perform an update or delete operation, if the condition is not true, i.e. there is no data (WHEN NOT MATCHED), we can perform an insert operation (INSERT data addition), also if the main table contains data that is not in the source table (or query result), we can perform an update or delete operation.

In addition to the main conditions listed above, we can specify “Additional Search Conditions”, they are specified via the keyword AND.

Simplified MERGE syntax

MERGE <baseline tab>
     USING <Source tab or request>
     ON <bonding conds>
     [ WHEN MATCHED ]
     THEN <UPDATE or DELETE>
     [ WHEN NOT MATCHED [ AND additional cond ] ]
     THEN <INSERT> ]
     [ WHEN NOT MATCHED BY SOURCE ]
     THEN <UPDATE or DELETE> ] [...n ]
     [ OUTPUT ]
;

Important points when using MERGE:

• At the end of the MERGE instruction, the semicolon (;) must go (;) otherwise an error will occur;
• There must be at least one MATCHED condition;
• The MERGE operation can be used in conjunction with CTE (generalized table expression);
• The MERGE instruction can use the OUTPUT keyword to see what changes have been made. To identify the operation here in OUTPUT you can use the $action variable;
• All operations on the main table that are provided for in MERGE (delete, insert or update) are subject to all restrictions defined for this table;
• The @@ROWCOUNT function, if used after the MERGE instruction, will return the total number of rows inserted, updated and deleted;
• In order to use MERGE, the INSERT, UPDATE or DELETE resolution in the main table and the SELECT resolution for the source table are required;
• When using MERGE, note that all AFTER triggers on INSERT, UPDATE or DELETE defined for the target table will be started.

Now let’s move on to practice. And first, let’s define the raw data.

Input data for examples of MERGE operation

I will have Microsoft SQL Server 2016 Express as my SQL server. It has a test database where I create test tables, for example, with products: TestTable_1 – we will have a target table, i.e. the one on which we will make all the changes, and TestTableDop_1 – the source table, i.e. the data according to which we will make the changes.

Request to create tables:

-- Goal tab
CREATE TABLE dbo.TestTable_1(
     ProdId INT NOT NULL,
     ProdName_1 VARCHAR(50) NULL,
     Summa MONEY NULL,
CONSTRAINT PK_TestTable_1 PRIMARY KEY CLUSTERED (ProductId ASC)
)
-- Source table
CREATE TABLE dbo.TestTableDop_1(
     ProdId INT NOT NULL,
     ProdName VARCHAR(50) NULL,
     Summa MONEY NULL,
CONSTRAINT PK_TestTableDop_1 PRIMARY KEY CLUSTERED (ProdId ASC)
)

Then I fill them with test data.

-- Add data to the main tab
INSERT INTO dbo.TestTable_1
          (ProdId,ProdName,Summa)
     VALUES
          (1, 'Computer', 0)
GO
INSERT INTO dbo.TestTable_1
          (ProdId,ProdName,Summa)
     VALUES
           (2, 'Printer', 0)
GO
INSERT INTO dbo.TestTable_1
           (ProdId,ProdName,Summa)
     VALUES
           (3, 'Monitor', 0)
GO
-- Add data to source table
INSERT INTO dbo.TestTableDop_1
          (ProdId,ProdName,Summa)
     VALUES
          (1, 'Computer', 500)
GO
INSERT INTO dbo.TestTableDop_1
          (ProdId,ProdName,Summa)
     VALUES
          (2, 'Printer', 300)
GO
INSERT INTO dbo.TestTableDop_1
          (ProdId,ProdName,Summa)
     VALUES
          (4, 'Monitor', 400)
GO

Let’s take a look at this data.

SELECT * FROM dbo.TestTable_1
SELECT * FROM dbo.TestTableDop_1

You can see that in the target table the value of field Summa = 0, as well as there is a mismatch of some identifiers, ie, we have products that are in one table, while they are not in another.

Updating and adding data using MERGE

This is probably a classic use of MERGE, when we update the data by merging, and if there is no such data, we add it. For the sake of clarity, at the end of the MERGE instruction, I’ll specify the keyword OUTPUT to see what changes we’ve made and also make a sample of the final data.

MERGE dbo.TestTable_1 AS T_Base -- Goal Table
USING dbo.TestTableDop_1 AS T_Source -- Source Table
ON (T_Base.ProdId = T_Source.ProdId) -- Unification condition
WHEN MATCHED THEN -- If the truth is (UPDATE)
     UPDATE SET ProdName = T_Source.ProdName, Summa = T_Source.Summa
WHEN NOT MATCHED THEN -- If not the truth (INSERT)
     INSERT (ProdId, ProdName, Summa)
     VALUES (T_Source.ProdId, T_Source.ProdName, T_Source.Summa)
-- Let's see what we did.
OUTPUT $action AS, Inserted.ProductId,
          Inserted.ProdName AS ProdNameNEW,
          Inserted.Summa AS SummaNEW,
          Deleted.ProdName AS ProdNameOLD,
          Deleted.Summa AS SummaOLD; -- Don't forget the semicolon
--Net result
SELECT * FROM dbo.TestTable_1
SELECT * FROM dbo.TestTableDop_1

We can see that we had two UPDATE operations and one INSERT. This is the case, two rows from the TestTable table correspond to two rows in the TestTableDop table, i.e. they have the same ProductId, we have updated the ProductName and Summa fields for these rows in the TestTable table. At the same time, the TestTableDop table has a row that is not in TestTable, so we have added it via INSERT.

Synchronizing tables using MERGE

Now, let’s say we need to synchronize the TestTable table with the TestTableDop table, to do that we will add another WHEN NOT MATCHED BY SOURCE condition, the point is that we will remove rows that are in TestTable but not in TestTableDOP.

But first, in order for all three conditions to work (particularly WHEN NOT MATCHED), let’s remove the row that we added in the previous example from the TestTable table. Also here I will specify the query as the source so that you can see how you can use the query as a source.

-- Deleting a string from ProdId = 4
-- To make the WHEN NOT MATCHED condition work.
DELETE dbo.TestTable_1 WHERE ProdId = 4
-- MERGE request for table synchronization
MERGE dbo.TestTable_1 AS T_Base -- Goal Table
-- A request as a source
USING (SELECT ProdId, ProdName, Summa)
     FROM dbo.TestTableDop_1) AS T_Source (ProdId, ProdName, Summa)
ON (T_Base.ProdId = T_Source.ProdId) -- Unification condition
WHEN MATCHED THEN -- If the truth is (UPDATE)
     UPDATE SET ProdName = T_Source.ProdName, Summa = T_Source.Summa
WHEN NOT MATCHED THEN -- If not true (INSERT)
     INSERT (ProdId, ProdName, Summa)
     VALUES (T_Source.ProdId, T_Source.ProdName, T_Source.Summa)
-- Delete strings if they're not in TestTableDOP_1.
WHEN NOT MATCHED BY SOURCE THEN
     DELETE
-- Let's see what we did.
OUTPUT $action AS [Operation], Inserted.ProdId, Inserted.ProdName AS ProdNameNEW,
     Inserted.Summa AS SummaNEW,Deleted.ProdName AS ProdNameOLD,
      Deleted.Summa AS SummaOLD; -- Don't forget the semicolon
-- Net result
SELECT * FROM dbo.TestTable_1
SELECT * FROM dbo.TestTableDop_1

As a result, we can see that our tables contain the same data. For this purpose, we have performed two UPDATE operations, one INSERT and one DELETE. In doing so, we used only one MERGE instruction.

Operation MERGE with additional condition

Now let’s perform a query similar to the one we used in Example 1, just add an additional condition to update the data, e.g. we will update TestTable only if the Summa field in TestTableDop contains some data (e.g. we don’t want to use incorrect values to update).

To see how this condition worked, let’s clean up the Summa field (set NULL) on one line in the TestTableDop table.

-- Clear the amount field from one line in TestTableDop_1.
UPDATE dbo. TestTableDop_1 SET Summa = NULL
WHERE ProdId = 2
-- MERGE request
MERGE dbo.TestTable_1 AS T_Base -- Goal Table
USING dbo.TestTableDop_1 AS T_Source - Source Table
ON (T_Base.ProdId = T_Source.ProdId) -- Unification condition
--If the truth + additional condition has worked (UPDATE).
WHEN MATCHED AND T_Source.Summa IS NOT NULL THEN
     UPDATE SET ProdName = T_Source.ProdName, Summa = T_Source.Summa
WHEN NOT MATCHED THEN -- If not the truth (INSERT)
     INSERT (ProdId, ProdName, Summa)
     VALUES (T_Source.ProdId, T_Source.ProdName, T_Source.Summa)
-- Let's see what we did.
OUTPUT $action AS, Inserted.ProdId,
          Inserted.ProdName AS ProdNameNEW,
          Inserted.Summa AS SummaNEW,
          Deleted.ProdName AS ProdNameOLD,
          Deleted.Summa AS SummaOLD; -Don't forget the semicolon
--net result
SELECT * FROM dbo.TestTable_1
SELECT * FROM dbo.TestTableDop_1

As a result, I have only two rows updated, moreover, all three rows have successfully met the merge condition, but one row has not updated, because the additional condition Summa IS NOT NULL has triggered, because the Summa field in the row with ProductId = 2, in the table TestTableDop, does not contain any data, ie NULL.

Merge in SQL Server