One of the fantastic new features of SQL Server 2008 is Merge Statement. Using a single statement, we can Add/Update records in our database table, without explicitly checking for the existence of records to perform operations like Insert or Update.
Facts about Merge Statement
Here are a few facts that you must know before starting to use Merge Statement:
- Atomic statement combining
DELETE operations based on conditional logic
- Done as a set-based operation; more efficient than multiple separate operations
MERGE is defined by ANSI SQL; you will find it in other database platforms as well
- Useful in both OLTP and Data Warehouse environments
OLTP: merging recent information from external source
DW: incremental updates of fact, slowly changing dimensions.
A typical merge statement looks like:
MERGE [INTO] <target table>
USING <source table or table expression>
ON <join/merge predicate> (semantics similar to outer join)
WHEN MATCHED <statement to run when match found in target>
WHEN [TARGET] NOT MATCHED <statement to run when no match found in target>
MERGE INTO dbo.tbl_Customers AS C
USING dbo.tbl_CustomersTemp AS CT
ON C.CustID = CT.CustID
WHEN MATCHED THEN
C.CompanyName = CT.CompanyName,
C.Phone = CT.Phone
WHEN NOT MATCHED THEN
INSERT (CustID, CompanyName, Phone)
VALUES (CT.CustID, CT.CompanyName, CT.Phone)
CREATE TABLE dbo.tbl_Source (id INT, name NVARCHAR(100), qty INT);
CREATE TABLE dbo.tbl_Target (id INT, name NVARCHAR(100), qty INT);
MERGE INTO dbo.tbl_Target AS t
USING dbo.tbl_Source AS s
ON t.id = s.id
WHEN MATCHED AND (t.name != s.name OR t.qty!= s.qty) THEN
UPDATE SET t.name = s.name, t.qty = s.qty
WHEN NOT MATCHED THEN
INSERT VALUES (s.id, s.name, s.qty)
WHEN SOURCE NOT MATCHED THEN
DELETE OUTPUT$action, inserted.id, deleted.id
So now with this new feature, we can implement the feature of add/insert/delete using a single statement without checking through the records.
Hope you enjoyed this article. Happy programming!!!
- 11th June, 2009: Initial post
Working as a Senior Consultant with Brilliance MSC, Malaysia.
Love to evaluate new technologies and implement the same.
Believe in sharing knowledge.