In this post I would like to finish my Change Data Capture practice. So I had not described the update of history table. All details about database structure can be found in the early post http://borisfrolov.com/2012/10/28/change-data-capture-practice-experience-part-2/.
As I have told everything about architecture of the designed solution, in this post there will be code, code and code again ))) .
As update process is rather complex, I have to create cursor in a way to unite old version of updated record and new version in single row. Also, I do not need to process records that were deleted after update.
Declare Cur cursor local FORWARD_ONLY READ_ONLY for
select
tab1.ID ID, tab1.LocationID oldLocationID,tab1.BusinessUnitID oldBusinessUnitID,tab1.RateID oldRateID, tab1.RateDate oldRateDate,tab1.RatePlan oldRatePlan, tab1.RateFact oldRateFact,tab2.LocationID newLocationID, tab2.BusinessUnitID newBusinessUnitID, tab2.RateID newRateID, tab2.RateDate newRateDate,tab2.RatePlan newRatePlan, tab2.RateFact newRateFact
from cdc.fn_cdc_get_all_changes_dbo_Doc_ABSMainRates (@from_lsn, @to_lsn, 'all update old') tab1 –- old version of the record (before update)
left join cdc.fn_cdc_get_all_changes_dbo_Doc_ABSMainRates (@from_lsn, @to_lsn, 'all update old') tab2 on tab2.__$start_lsn = tab1.__$start_lsn and tab2.__$seqval = tab1.__$seqval –- new version of the record (after update)
where tab1.__$operation = 3 and tab2.__$operation = 4 and
not exists (
-- Remove records that were deleted after update
select tab3.ID
from cdc.fn_cdc_get_all_changes_dbo_Doc_ABSMainRates (@from_lsn, @to_lsn, 'all update old') tab3
where tab1.ID = tab3.ID and tab3.__$operation = 1 -– delete operation
)
-- Declare variables to use in cursor processing
Declare @ID int, @oldLocationID int, @oldBusinessUnitID int, @oldRateID int, @oldRatePlan int, @oldRateFact int, @newLocationID int, @newBusinessUnitID int, @newRateID int, @newRatePlan int, @newRateFact int, @oldRateDate datetime, @newRateDate datetime
Declare @oldABSRateID int, @newABSRateID int
Now I’m ready to open cursor and start table update.
open Curfetch Cur into @ID, @oldLocationID, @oldBusinessUnitID, @oldRateID, @oldRateDate, @oldRatePlan, @oldRateFact, @newLocationID, @newBusinessUnitID, @newRateID, @newRateDate, @newRatePlan, @newRateFact
while @@fetch_status=0 begin -- Get the old ID, in order to make link from ABSMainRates_History table
select @oldABSRateID = (
select top 1 __IDfrom BIWarehouse.dbo.ABSMainRates abs1
where ABSMainRateID = @IDorder by abs1.rev desc
)
-- Add new record to ABSMainRates with version (Rev field) max previous value + 1
insert into BIWarehouse.dbo.ABSMainRates (
__LastUpdatedTime,
ABSMainRateID,
Rev
)
select GetDate(), @ID, (select max(abs1.Rev) + 1
from BIWarehouse.dbo.ABSMainRates abs1where ABSMainRateID = @ID) -- Get the new ID, in order to make link from ABSMainRates_History table
select @newABSRateID = (select top 1 __IDfrom BIWarehouse.dbo.ABSMainRates abs1where ABSMainRateID = @IDorder by abs1.rev desc)
-- Update Current_ABSMainRates table with new values and link to ABSMainRate table
-- Of course select top 1 for ref books does not look very great, so I’ll have to make some improvements in the future.
update BIWarehouse.dbo.Current_ABSMainRates set
__LastUpdatedTime = GetDate(),
ABSMainRate = @newABSRateID,
Date = convert(date, GetDate()),
Location = (select top 1 loc.__IDfrom BIWarehouse.dbo.Ref_Location locwhere loc.LocationID = @newLocationID),
BusinessUnit = (select top 1 bu.__IDfrom BIWarehouse.dbo.Ref_BusinessUnit buwhere bu.BusinessUnitID = @newBusinessUnitID),
Rate = (select top 1 rate.__IDfrom BIWarehouse.dbo.Ref_Rate ratewhere rate.RateID = @newRateID),
ChangedBy = (select top 1 users.__IDfrom BIWarehouse.dbo.Ref_User userswhere users.Name = 'System'),
RatePlan = @newRatePlan,
RateFact = @newRateFact,
RateDate = @newRateDate
where BIWarehouse.dbo.Current_ABSMainRates.ABSMainRate = @oldABSRateID
-- Fill table ABSMainRates_History with negative values from the previous record version
insert into BIWarehouse.dbo.ABSMainRates_History (
__LastUpdatedTime,
RecordCount,
ABSMainRate,
Date,
Location,
BusinessUnit,
Rate,
ChangedBy,
RatePlan,
RateFact,
RateDate
)
selectGetDate(), -1, @oldABSRateID, convert(date, GetDate()), (select top 1 loc.__ID
from BIWarehouse.dbo.Ref_Location loc
where loc.LocationID = @oldLocationID) LocationID,
(select top 1 bu.__ID
from BIWarehouse.dbo.Ref_BusinessUnit bu
where bu.BusinessUnitID = @oldBusinessUnitID) BusinessUnitID,
(select top 1 rate.__ID
from BIWarehouse.dbo.Ref_Rate rate
where rate.RateID = @oldRateID) RateID,
(select top 1 users.__ID
from BIWarehouse.dbo.Ref_User users
where users.Name = 'System') UserID,
-@oldRatePlan, -@oldRateFact, @oldRateDate
-- Fill table ABSMainRates_History with positive values from the current record version
insert into BIWarehouse.dbo.ABSMainRates_History (
__LastUpdatedTime,
RecordCount,
ABSMainRate,
Date,
Location,
BusinessUnit,
Rate,
ChangedBy,
RatePlan,
RateFact,
RateDate
)
select GetDate(), 1, @newABSRateID, convert(date, GetDate()),
(select top 1 loc.__ID
from BIWarehouse.dbo.Ref_Location loc
where loc.LocationID = @newLocationID) LocationID,
(select top 1 bu.__ID
from BIWarehouse.dbo.Ref_BusinessUnit bu
where bu.BusinessUnitID = @newBusinessUnitID) BusinessUnitID,
(select top 1 rate.__ID
from BIWarehouse.dbo.Ref_Rate rate
where rate.RateID = @newRateID) RateID,
(select top 1 users.__ID
from BIWarehouse.dbo.Ref_User users
where users.Name = 'System') UserID,
@newRatePlan, @newRateFact, @newRateDate
So that’s all – I’ve met the designed requirements. Now I have to close cursor and save the LSN till we made the synchronization.
fetch Cur into @ID, @oldLocationID, @oldBusinessUnitID, @oldRateID, @oldRateDate, @oldRatePlan, @oldRateFact, @newLocationID, @newBusinessUnitID, @newRateID, @newRateDate, @newRatePlan, @newRateFact end
close Cur
deallocate Cur
------------------------------------------------------------ Save the LSN till we made a synchronization.----------------------------------------------------------
update BIWarehouse.dbo.Service_WarehouseConfig
set LastSLN = @to_lsn
where ID = 'Doc_ABSMainRates table'
So that’s all I wanted to tell about my first working solution based on Change Data Capture. I do not use this functionality in other systems, as they are still working on SQL Server 2005.