ETL with T-SQL Merge Statement

I sometimes use ETL through pure SQL (no SSIS). I use the following.

We have two parts:
1. First connect to the OLTP/ODS server from the DW/ETL server through OPENROWSET (I use this instead of OPENQUERY, since OPENQUERY requires Linked Servers). To enable OPENROWSET just enable Ad Hoc Distributed Queries, as follows:
EXEC sp_configure ‘show advanced options’, 1;
GO
reconfigure;
GO

EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1
GO
reconfigure;
GO

2. Design the Merge: For all types of Slowly Changing Dimensions:

MERGE [DWTable] bo
using OPENROWSET(‘SQLNCLI’,‘Server=OLTP1;Database=DB1;Uid=User1;Pwd=Pwd123;’
, ‘SELECT * FROM  T1’) bi
ON bi.ID = bo.ID
WHEN matched AND bi.firstName =  THEN
DELETE
WHEN MATCHED then
UPDATE set    bi.Amount = bi.Amount + bo.Amount

WHEN NOT matched BY target THEN
INSERT ( bo.C1, bo.C2)
VALUES (bi.C1, bi.C2)
WHEN NOT matched BY source THEN
DELETE;
GO

Leave a Reply

Your email address will not be published. Required fields are marked *