Not logged in - Login

SQL 2008 Composable DML

Composable DML Another powerful T-SQL enhancement in SQL Server 2008 is the ability to write composable DML. It allows to consume the OUTPUT clause result set and to feed it as source for a query. 

/*
 
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));

CREATE TABLE AccountsAudit (
account_nbr INT,
change_action NVARCHAR(10),
change_date DATETIME DEFAULT CURRENT_TIMESTAMP,
old_company_name VARCHAR(35),
new_company_name VARCHAR(35),
PRIMARY KEY(account_nbr, change_action, change_date));
 
*/
 

-- 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
 


INSERT INTO AccountsAudit
( account_nbr, change_action, old_company_name, new_company_name)
SELECT account_nbr, merge_action, old_company_name, new_company_name
FROM (MERGE INTO CentralOfficeAccounts AS C
USING BranchOfficeAccounts AS B ON C.account_nbr = B.account_nbr
WHEN MATCHED
AND C.company_name <> B.company_name THEN
 
-- do an update
UPDATE SET C.company_name = B.company_name
WHEN NOT MATCHED THEN
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
OUTPUT $action,
COALESCE(inserted.account_nbr, deleted.account_nbr), -- returns the first non-null argument in the list.
deleted.company_name,
inserted.company_name
) AS T(merge_action, account_nbr,
old_company_name, new_company_name);

SELECT account_nbr, change_action, change_date,
old_company_name, new_company_name
FROM AccountsAudit;
 

/*
 
DROP TABLE CentralOfficeAccounts, BranchOfficeAccounts, AccountsAudit; 

*/