Change data capture. Practice experience. Part 3.

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 https://borisfrolovsqlservertips.wordpress.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 Cur
fetch 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 __ID
from BIWarehouse.dbo.ABSMainRates abs1
where ABSMainRateID = @ID
order 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 abs1
where ABSMainRateID = @ID
)
 
— Get the new ID, in order to make link from ABSMainRates_History table
       select @newABSRateID = (
select top 1 __ID
from BIWarehouse.dbo.ABSMainRates abs1
where ABSMainRateID = @ID
order 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.__ID
from BIWarehouse.dbo.Ref_Location loc
where loc.LocationID = @newLocationID
),
             BusinessUnit = (
select top 1 bu.__ID
from BIWarehouse.dbo.Ref_BusinessUnit bu
where bu.BusinessUnitID = @newBusinessUnitID
),
             Rate = (
select top 1 rate.__ID
from BIWarehouse.dbo.Ref_Rate rate
where rate.RateID = @newRateID
),
              ChangedBy = (
select top 1 users.__ID
from BIWarehouse.dbo.Ref_User users
where 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
       )
       select
GetDate(), -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.

Advertisements
This entry was posted in Database development, SQL Server, Technology and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s