SQL 2008 Merge statement sample

Some samples on how to use the MERGE statement that was introduced with SQL 2008:

CREATE TABLE CentralOfficeAccounts (
account_nbr INT PRIMARY KEY,
company_name VARCHAR(35),
primary_contact VARCHAR(35),
contact_phone VARCHAR(12));
 
CREATE TABLE BranchOfficeAccounts (
account_nbr INT PRIMARY KEY,
company_name VARCHAR(35),
primary_contact VARCHAR(35),
contact_phone VARCHAR(12));
 

-- Insert sample data 

INSERT INTO CentralOfficeAccounts
VALUES (1, 'Bayside Motors', 'Jim Smith', '902-203-1234'),
(2, 'Dallas Industries', 'Joe Doe', '301-663-9134'),
(3, 'Sky Computer Systems', 'Jane Brown', '201-943-6053');
 
INSERT INTO BranchOfficeAccounts
VALUES (2, 'Dallas Industries, Inc.', 'Rick Gross', '301-663-9134'), -- changed
(3, 'Sky Computer Systems', 'Jane Brown', '201-943-6053'), -- same
(4, 'Auto Insurance Co.', 'Chris Jefferson', '313-601-6201'); -- new
 
-- Merge, slide 1
-- 1) Update existing and add missing
 
BEGIN TRAN

MERGE INTO CentralOfficeAccounts AS C -- Target
USING BranchOfficeAccounts AS B ON C.account_nbr = B.account_nbr -- Source
WHEN MATCHED THEN -- On match update
UPDATE SET C.company_name = B.company_name,
C.primary_contact = B.primary_contact,
C.contact_phone = B.contact_phone
WHEN NOT MATCHED THEN -- Add missing
INSERT (account_nbr, company_name, primary_contact, contact_phone)
VALUES (B.account_nbr, B.company_name, B.primary_contact, B.contact_phone);
SELECT account_nbr, company_name, primary_contact, contact_phone 
FROM CentralOfficeAccounts; 


ROLLBACK TRAN


-- Merge, slide 2
-- 2) Update existing that changed and add missing
 -- Use of predicates 

BEGIN TRAN

 
MERGE INTO CentralOfficeAccounts AS C -- Target
USING BranchOfficeAccounts AS B ON C.account_nbr = B.account_nbr -- Source
WHEN MATCHED -- On match update
AND -- Additional search conditions
( C.company_name <> B.company_name 
OR C.primary_contact <> B.primary_contact 
OR C.contact_phone <> B.contact_phone) THEN 
UPDATE SET C.company_name = B.company_name,
C.primary_contact = B.primary_contact,
C.contact_phone = B.contact_phone
WHEN NOT MATCHED THEN -- Add missing
INSERT (account_nbr, company_name, primary_contact, contact_phone)
VALUES (B.account_nbr, B.company_name, B.primary_contact, B.contact_phone);
SELECT account_nbr, company_name, primary_contact, contact_phone 
FROM CentralOfficeAccounts; 

ROLLBACK TRAN



-- Merge, slide 3 
-- 3) Update existing that changed and add missing, delete missing in source
 
BEGIN TRAN

 
MERGE
 INTO CentralOfficeAccounts AS C -- Target
USING BranchOfficeAccounts AS B ON C.account_nbr = B.account_nbr -- Source 
WHEN MATCHED -- On match update
AND (C.company_name <> B.company_name -- Additional search conditions
OR C.primary_contact <> B.primary_contact
OR C.contact_phone <> B.contact_phone) THEN 
UPDATE SET C.company_name = B.company_name,
C.primary_contact = B.primary_contact,
C.contact_phone = B.contact_phone
WHEN NOT MATCHED THEN -- Add missing
INSERT (account_nbr, company_name, primary_contact, contact_phone)
VALUES (B.account_nbr, B.company_name, B.primary_contact, B.contact_phone)
WHEN NOT MATCHED BY SOURCE THEN -- Delete missing from source
DELETE;
 

SELECT
 account_nbr, company_name, primary_contact, contact_phone 
FROM CentralOfficeAccounts; 

ROLLBACK TRAN


 
DROP TABLE CentralOfficeAccounts, BranchOfficeAccounts;