SQL Server 2008 T-SQL: MERGE Statement
SQL Server 2008 T-SQL: MERGE Statement
I've seen many applications that needs to apply changes to the database from a list in the memory - we have a list of entities with ID's and other data (lets call it the source), and we want to update a target table in the database to have the same data. In order to achieve this, we have to perform the following logic:
- If the source list has a record that matches a record in the target table, we want to update that record according to the changes in the memory.
- If the source list has a record that doesn't match any record in the target table, we want to insert a new record to the table.
Sometimes, we would also like to have the following logic:
- If the target table has a row that does match any row in the source list, we want to delete that row from the database.
Today, in order to apply changes from a source table to a target table we usually use stored procedure with complicated logic that is hard to develop, test and maintain.
In SQL Server 2008, a new T-SQL statement is introduced: the MERGE statement, that can help us face the above challenges. To understand the full strength of the MERGE statement, lets look into an example:
Lets say we have a Customers table:
create table Customers
(
CustomerID int not null primary key identity(1,1),
Name varchar(50) not null,
City varchar(50) null,
Phone varchar(10) null,
)
with the following data:
and we want to apply the changes from a temporary table. For example, we have a CustomerTableType
create type CustomerTableType as table
(
CustomerID int,
Name varchar(50),
City varchar(50),
Phone varchar(10)
)
and we declared a new table with some rows:
declare @CustomerTable CustomerTableType;
insert into @CustomerTable (CustomerID, Name, City, Phone)
values
(1, 'Customer New Name', 'Jerusalem', '2343245'),
(2, 'Customer #2', 'Tel Aviv', '0987345'),
(4, 'Customer #4', 'Zefat', '2345543');
Notice that if we try to match between rows from the source table (the temporary table) and the target table we want to apply the changes to, we find that:
- Customer #1 was changed - name changed from 'Customer #1' to 'Customer New Name'
- Customer #2 looks just like the row in the database
- Customer #3 is missing from the temporary table
- Customer #4 exists only in the temporary table and not in the database.
In order to make the database contain this data, we have to perform the following changes:
- Update Customer #1
- Do nothing to Customer #2
- Delete Customer #3
- Insert Customer #4
We can do this in a single statement in SQL Server 2008:
merge into Customers c
using @CustomerTable t on c.CustomerID = t.CustomerID
when matched then update
set c.Name = t.Name,
c.City = t.City
when not matched then insert
(Name, City, Phone)
values
(t.Name, t.City, t.Phone)
when source not matched then delete;
This statement treats the temporary table t as the source table, and the database Customers table as the target table c. It tries to match rows from the tables using the primary key of CustomerID, and perform exactly the wanted logic:
- If the source list has a record that matches a record in the target table, it will update that record according to the changes in the memory. (Customer #2)
- If the source list has a record that doesn't match any record in the target table, we want to insert a new record to the table. (Customer #4)
- If the target table has a row that does match any row in the source list, we want to delete that row from the database. (Customer #3).
Finally, the data in the database table will be:
Very nice!