Effective Merge Statements in SQL

-- Step 1: Create and Populate Customers table.

-- Note: Population uses row constructors, which are new in SQL Server 2008

--

CREATE TABLE dbo.Customers (

customer_id int IDENTITY PRIMARY KEY,

customer_name varchar(30),

policy_type int CHECK(policy_type > 0 AND policy_type < 4),

deductable money,

number_of_claims int

)

　

INSERT Customers VALUES

('Brian Smith', 1, 1000, 0),

('David Johnson', 1, 5000, 0),

('Lisa Miller', 2, 2000, 1),

('Nancy Anderson', 2, 1000, 1)

　

--

-- Step 2: Create and Populate QuarterlyUpdate table.

-- Note: Population uses row constructors, which are new in SQL Server 2008

-- Note: Not every Customer must appear in the QuarterlyUpdate table.

--



CREATE TABLE QuarterlyUpdate (

customer_id int,

customer_name varchar(30),

policy_type int CHECK(policy_type > 0 AND policy_type < 4),

deductable money,

new_claims int

)



INSERT QuarterlyUpdate VALUES

(1, 'Brian Burke', 1, 1000, 0),

(2, 'David Johnson', 1, 5000, 2),

(5, 'Michelle Fredette', 2, 2000, 0), -- New Customer

(6, 'Phil Gibbins', 2, 2000, 0) -- New Customer

　

SELECT * FROM Customers

SELECT * FROM QuarterlyUpdate

--

-- Step 3: MERGE

-- Note: No action is taken when row appears in Customer but not QuarterlyUpdate

-- Note: Uses increment operator, new in SQL Server 2008 T-SQL

--

MERGE Customers C

USING QuarterlyUpdate Q

ON C.customer_id = Q.customer_id

WHEN MATCHED THEN

UPDATE SET C.customer_name = Q.customer_name,

C.policy_type = Q.policy_type,

C.deductable = Q.deductable,

C.number_of_claims += new_claims

WHEN NOT MATCHED THEN

INSERT VALUES(customer_name, policy_type, deductable, new_claims)

OUTPUT $action, INSERTED.customer_id,

INSERTED.customer_name AS [New Name],

INSERTED.policy_type AS [New Policy],

INSERTED.deductable AS [New Deductable],

INSERTED.number_of_claims AS [New Number Of Claims],

DELETED.customer_id AS [Original Customer Id],

DELETED.customer_name AS [Original Name],

DELETED.policy_type AS [Original Policy],

DELETED.deductable AS [Original Deductable],

DELETED.number_of_claims AS [Original Number Of Claims];

DELETE QuarterlyUpdate;

--

-- Step 5: Next quarter, a new business rule is introduced.

-- A row for each current customer is required in QuarterlyUpdate

-- If no row is present, customer will be deleted

--

INSERT QuarterlyUpdate VALUES

(1, 'Brian Burke', 1, 1000, 0),

(2, 'David Johnson', 1, 5000, 2),

(3, 'Lisa Miller', 2, 2000, 1),

(5, 'Michelle Fredette', 2, 2000, 1),

(6, 'Phil Gibbins', 2, 2000, 0),

(7, 'Sam Raymond', 3, 10000, 0)

　

--

-- Step 3: MERGE

-- Note: When row appears in Customer but not QuerterlyUpdate, Customer is deleted

-- Note: Uses increment operator, new in SQL Server 2008 T-SQL

-- Note: Uses WHEN NOT MATCHED BY SOURCE - keyword SOURCE is required

-- and Uses WHEN NOT MATCHED BY TARGET - keyword TARGET is optional

--

MERGE Customers C

USING QuarterlyUpdate Q

ON C.customer_id = Q.customer_id

WHEN MATCHED THEN

UPDATE SET C.customer_name = Q.customer_name,

C.policy_type = Q.policy_type,

C.deductable = Q.deductable,

C.number_of_claims += new_claims

WHEN NOT MATCHED BY SOURCE THEN

DELETE

WHEN NOT MATCHED BY TARGET THEN

INSERT VALUES(customer_name, policy_type, deductable, new_claims)

OUTPUT $action, INSERTED.customer_id,

INSERTED.customer_name AS [New Name],

INSERTED.policy_type AS [New Policy],

INSERTED.deductable AS [New Deductable],

INSERTED.number_of_claims AS [New Number Of Claims],

DELETED.customer_id AS [Original Customer Id],

DELETED.customer_name AS [Original Name],

DELETED.policy_type AS [Original Policy],

DELETED.deductable AS [Original Deductable],

DELETED.number_of_claims AS [Original Number Of Claims];

SELECT * FROM Customers

SELECT * FROM QuarterlyUpdate