MERGE Statement in SQL Server 2008
While working with table records from more than one table, there is often need of synchronizing data across many tables. For this purpose, we generally write UPDATE, INSERT and DELETE commands one after another based upon certain conditions. Oracle has already MERGE command to perform inserts and updates into a single table in a single command since Oracle9i. But with SQL Server 2008, there is MERGE command to do updates, inserts and deletes in a single statement. MERGE statement will take the source data from a table or view or query, and then perform DML operations (insert, update, delete) if the right conditions match.
Let’s see from an example where we have two tables: dbo.BusinessClients_Old as ‘Target’ table and dbo.BusinessClients as ‘Source’ table. We are going to synchronize the records between these two tables. We will update ‘ContactNo’ in Target table, insert new record into Target table that is only in Source table and delete unmatched record in Target table. The result will be synchronized records between both the tables.
Fig 1: Initial data found in both the tables
Now, issue a MERGE command as:
MERGE dbo.BusinessClients_Old as tblTarget
USING dbo.BusinessClients as tblSource
ON (tblTarget.ID = tblSource.ID)
WHEN MATCHED THEN
UPDATE SET tblTarget.ContactNo = tblSource.ContactNo
WHEN NOT MATCHED BY SOURCE THEN
WHEN NOT MATCHED THEN
INSERT (Name, Email, Profession, ContactNo, ID)
VALUES (tblSource.Name, tblSource.Email, tblSource.Profession, tblSource.ContactNo, tblSource.ID)
OUTPUT $action, inserted.id as InsertedId, inserted.name AS InsertedName, inserted.contactno AS InsertedContactNo,
deleted.id AS DeletedId, deleted.name AS DeletedName;
The result is shown in figure below. Fig 2: Result of MERGE command that is synchronized records now
See the updated ContactNo and inserted new record in the Target table: dbo.BusinessClients_Old. Fig 3: Synchronize records in Target table: dbo.BusinessClients_Old
Points to be noted:
MERGE statement must end with a semicolon. The OUTPUT clause is optional here which is used to show recently inserted or updated or deleted records in the same session by INSERT or UPDATE or DELETE statement. This OUTPUT clause is there since SQL Server 2005. We may be aware of ‘inserted’ and ‘deleted’ tables if we have made our hands dirty by writing triggers. ‘inserted’ and ‘deleted’ are temporary tables created automatically by SQL Server whenever there is row modification in a table.
We can remove the OUTPUT clause, but it is there to see what all modifications happen when issuing MERGE command. ‘$action’ is a nvarchar(10) column that shows which action was performed by the MERGE command: INSERT or UPDATE or DELETE based upon the condition specified. Better to write ‘$action’ always in lower case as its case is dependent upon database collation for case sensitiveness. For WHEN clause, we have to be little careful. I suggest to refer to SQL Server Books Online at http://msdn.microsoft.com/en-us/library/ms130214.aspx for more details on WHEN clause.