Update SCD Type 2 dimension in one single transaction using only T-SQL

Posted on

Recently I got a request inside my organization to make sure that a dimension would keep track of the changes due to requrementes from the business. This needed to be done in a single transaction in pure T-SQL code. So – what to do and how to do it. Here’s one way. The sourcetable looks like this: The request was to keep track of changes in the ManagerId according to CaseId. I’ve created a SCD2 table like this: CREATE TABLE [dbo].[CaseProjectManagerHistory]( [dwid] [bigint] IDENTITY(1,1) NOT NULL, [CaseId] [int] NULL, [ManagerId] [int] NULL, [dwDateFrom] [date] NULL, [dwDateTo] [date] NULL, [dwIsCurrent] [bit] […]