Introduction
In my article Merge Statement in SQL Server 2008, I tried to showcase the new features of SQL Server 2008 wherein 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.
In this article, let me explain Composable DML, another cool feature of SQL Server 2008.
Here are a few facts that you must know before starting to use Composable DML:
- Combine multiple DML actions in one statement.
- Have one statement operate on rows affected by another.
- SQL Server 2008 introduces
INSERT FROM DML
.
Syntax
INSERT INTO [target table]
SELECT ...
FROM ([INSERT | UPDATE | DELETE | MERGE statement with OUTPUT clause]) AS D
WHERE … ;
Example
There is a Customer table in Head Office and also in Branch Office. Through End of Day processing, the Head Office table is updated with the Branch Office table data for changes like new customer registration and changes in existing customer info. The changes (New, Edit) are logged in an Audit table.
The example uses a Merge statement and Composable DML to perform update in the Head Office Customer table and also logs the updates in the Audit table.
CREATE TABLE tblCustomerMaster_HeadOffice(
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(100),
PrimaryContact VARCHAR(100),
ContactPhone VARCHAR(12));
CREATE TABLE tblCustomerMaster_BranchOffice(
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(100),
PrimaryContact VARCHAR(100),
ContactPhone VARCHAR(12));
CREATE TABLE tblCustomersAudit (
CustomerID INT,
ChangeAction NVARCHAR(10),
ChangeDate DATETIME DEFAULT CURRENT_TIMESTAMP,
OldCustomerName VARCHAR(100),
NewCustomerName VARCHAR(100),
PRIMARY KEY(CustomerID , ChangeAction , ChangeDate ));
INSERT INTO tblCustomersAudit(CustomerID, ChangeAction,
OldCustomerName, NewCustomerName)
SELECT CustomerID, MergeAction, OldCustomerName, NewCustomerName
FROM (
MERGE INTO tblCustomerMaster_HeadOffice AS HOC
USING tblCustomerMaster_BranchOffice AS BOC
ON
HOC.CustomerID = BOC.CustomerID
WHEN MATCHED AND HOC.CustomerName <> BOC.CustomerName
THEN
UPDATE SET HOC.CustomerName = BOC.CustomerName,
HOC.PrimaryContact = BOC.PrimaryContact,
HOC.PrimaryContact = BOC.ContactPhone
WHEN NOT MATCHED
THEN
INSERT (CustomerID, CustomerName, PrimaryContact, ContactPhone)
VALUES (BOC.CustomerID, BOC.CustomerName,
BOC.PrimaryContact, BOC.ContactPhone)
OUTPUT $action,
COALESCE(inserted.CustomerID, deleted.CustomerID),
deleted.CustomerName,
inserted.CustomerName
) AS T(MergeAction, CustomerID, OldCustomerName, NewCustomerName)
Conclusion
I tried to showcase Composable DML in action along with Merge statements. This is pretty simple and easy to implement.
Hope you enjoyed this article. Happy programming!!!