Programmer's Rules

Transact-SQL

MERGE operation in SQL Server

How to use MERGE statement to modify data in SQL Server

Transact-SQLjob interviewSQL Server70-761

MERGE single statement is used to modify data (insert, update and delete) in a destination table at the same time.

The following rules are taken into account when merging:

- if the source table data matches the data in the target table - the data in target is updated,

- if the source table data has no match in the data in the target table - the data is inserted to target,

- if the target table data has no match in the source - the data in target is deleted

Let's look at the two tables:

OldProduct

Name Price
SQL Server 2008 100
SQL Server 2012 200
SQL Server 2014 300

NewProduct

Name Price
SQL Server 2005 50
SQL Server 2012 200
SQL Server 2014 300
SQL Server 2016 300

If we want to leave all matching rows, insert rows absent in the NewProduct and delete those absent in the OldProduct the MERGE statement looks like:

After the operation, the destination table NewProduct has the following data:

Name Price
SQL Server 2008 100
SQL Server 2012 200
SQL Server 2014 300
Tutorial statistics