Articles → SQL SERVER → Merge Command In SQL Server

Merge Command In SQL Server

Consider an example, where you want to compare 2 data sources (view, table, etc.), and based on the comparison, you want to do any of the CRUD operations i.e., insert, update, or delete then what is the best way to do that?

The best possible solution that I feel is the use of merge command in SQL server.

Source And Target

Let us replace the term data source with table to make things simpler. Consider 2 tables that you want to compare. Let us name them as "Table 1" and "Table 2". There could be 2 possible conditions: -

Table 1 and Table 2 contain the same records
Table 1 and Table 2 contain distinct records

In the second scenario, if table synchronization is required then it could be done by inserting, updating, and deleting one table from another table. So, the table from where the CRUD operations are done is the Target table and the table where data is copied is the source table.

Syntax

-- Syntax
MERGE Table1 T using Table2 S ON S.column_name = T.column_name WHEN NOT MATCHED THEN INSERT(
  TargetColumn1, TargetColumn2,...
) 
VALUES 
  (
    SourceColumn1, SourceColumn2,...
  ) WHEN MATCHED THEN 
UPDATE 
SET 
  T.Column1 = S.Column1;

A merge statement should always terminate by a semicolon (;).

Sample

Consider a scenario where you are working on an offline system i.e., not connected to the internet. Occasionally you connect to the internet and update your database tables. Let us see, how to accomplish it using the merge command.

Consider 2 tables: -

OfficeSource
OfficeTarget

Here "OfficeTarget" is the table that is used when the system is offline. Once the system becomes online, synchronization occurs among the "OfficeSource" and "OfficeTarget" tables i.e., data will be inserted and updated in the "OfficeTarget" table.

Scenario 1 → "OfficeSource" contains one value and "OfficeTarget" does not have any value

Picture showing the output of the merge command where value does not exists

Now, run this query.

-- Query
MERGE OfficeTarget T using OfficeSource S ON S.office_id = T.office_id WHEN NOT MATCHED THEN INSERT(office_location) 
VALUES 
  (office_location) WHEN MATCHED THEN 
UPDATE 
SET 
  T.office_location = S.office_location;

Output will be

Picture showing the data merged between the 2 tables

Scenario 2 → Both tables contain distinct records.

Picture showing the output of the merge command when both tables contains different records on the same id

Now run the preceding query again and you get the following output

Scenario 3 → OfficeSource contains more records than OfficeTarget

Picture showing the output of the merge command when one table contains more records than other

Now run the preceding query again and you get the following output

Posted By -	Karan Gupta

Posted On -	Sunday, January 31, 2016

Query/Feedback

Your Email Id		**

Subject		*

Query/Feedback	Characters remaining 250	**