SQL Server: Insert, Update and Delete Data in a Single Execution by Using MERGE
With the release of SQL Server 2008, insert, update, or delete operations can be performed in a single statement using MERGE. Prior to SQL Server 2008, each insert, update or delete had to be run separately. The MERGE statement allows you to join a data source with a target table or view, and then perform multiple actions against the target based on the results of that join.
The following example is taken from TechNet, and the original example can be found at http://technet.microsoft.com/en-us/library/bb522522(v=sql.105).aspx This example uses MERGE to insert, update, or delete rows in a target table based on differences with the source data.
Consider a small company with five departments, each with a department manager. The company decides to re-organise its departments. To implement the re-organisation results in the target table dbo.Departments, the MERGE statement must implement the following changes:
- Some existing departments will not change.
- Some existing departments will have new managers.
- Some departments are newly created.
- Some departments will not exist after the reorganization.
The following code creates the target table dbo.Departments and populates it with managers.
IF OBJECT_ID (N'dbo.Departments', N'U') IS NOT NULL
DROP TABLE dbo.Departments;
CREATE TABLE dbo.Departments (DeptID tinyint NOT NULL PRIMARY KEY, DeptName nvarchar(30),
INSERT INTO dbo.Departments
VALUES (1, 'Human Resources', 'Margheim'),(2, 'Sales', 'Byham'),
(3, 'Finance', 'Gill'),(4, 'Purchasing', 'Barber'),
(5, 'Manufacturing', 'Brewer');
The organizational changes to be made to the departments are stored in the source table dbo.Departments_delta. The following code creates and populates this table:
IF OBJECT_ID (N'dbo.Departments_delta', N'U') IS NOT NULL
DROP TABLE dbo.Departments_delta;
CREATE TABLE dbo.Departments_delta (DeptID tinyint NOT NULL PRIMARY KEY, DeptName nvarchar(30),
INSERT INTO dbo.Departments_delta VALUES
(1, 'Human Resources', 'Margheim'), (2, 'Sales', 'Erickson'),
(3 , 'Accounting', 'Varkey'),(4, 'Purchasing', 'Barber'),
(6, 'Production', 'Jones'), (7, 'Customer Relations', 'Smith');
Finally, to reflect the company reorganization in the target table, the following code uses the MERGE statement to compare the source table, dbo.Departments_delta, with the target table dbo.Departments. The search condition for this comparison is defined in the ON clause of the statement. Based on the results of the comparison, the following actions are taken.
Departments that exist in both tables are updated in the target table with new names, new managers, or both in table Departments. If there are no changes, nothing is updated. This is accomplished in the WHEN MATCHED THEN clause.
Any departments in Departments_delta that don't exist in Departments are inserted into Departments. This is accomplished in the WHEN NOT MATCHED THEN clause.
Any departments in Departments that do not exist in the source table Departments_delta are deleted from Departments. This is accomplished in the WHEN NOT MATCHED BY SOURCE THEN clause.
MERGE dbo.Departments AS d
USING dbo.Departments_delta AS dd
ON (d.DeptID = dd.DeptID)
WHEN MATCHED AND d.Manager <> dd.Manager OR d.DeptName <> dd.DeptName
THEN UPDATE SET d.Manager = dd.Manager, d.DeptName = dd.DeptName
WHEN NOT MATCHED THEN
INSERT (DeptID, DeptName, Manager)
VALUES (dd.DeptID, dd.DeptName, dd.Manager)
WHEN NOT MATCHED BY SOURCE THEN
inserted.DeptID AS SourceDeptID, inserted.DeptName AS SourceDeptName,
inserted.Manager AS SourceManager,
deleted.DeptID AS TargetDeptID, deleted.DeptName AS TargetDeptName,
deleted.Manager AS TargetManager;