Merging Table Data Using Merging Statement In MSSQL 2012

In this blog, we will learn how we can merge the table data using a merging statement. Before I share, I would like to explain in which cases we can use the merging statements. MERGE statement works best when two tables have a complex mixture of matching characteristics; for example, inserting a row if it does not exist, or updating the row if it does match. When simply updating one table based on the rows of another table, improved performance and scalability can be achieved with basic INSERT, UPDATE, and DELETE statements.

Step 2

Now, insert the data into Products Table.

The table will be something like this.

Step 3

Now, create UpdatedProducts table; i.e., Source table which the table data will be merged into with the target table; i.e., with Products table.

Step 4

Now, insert data into UpdateProudcts table.

The result will be something like this.

Now, observe the two table that we created.

Step 5

Now, the Merge statement comes into the picture to make the changes which you want in your table.

Syntax

Initially, synchronize the target table with refreshed data from the source table.

When records are matched, update the records if there is any change.

When no records are matched, insert the incoming records from source table to target table.

When there is a row that exists in the target table and same record does not exist in the source table, then delete this record from the target table.

In the below code $action specifies a column of type nvarchar(10) in the OUTPUT clause that returns one of the three values for each row: 'INSERT', 'UPDATE', or 'DELETE', according to the action that was performed on that row. This is for our convenience to understand on which row the action is performed.

When we execute the Products table, UpdatedProducts table, and the Merge statement, the output will be something like this.

In the above result, TargetProductID is the target column on which the action is performed.