--Please note section 3 nneds to be run each time an Insert, Update or Delete is performed. --If section 3 is not run after each DML then only the last DML can be garaunteed. /* Section 1: Run to reset. This recreates the tables and makes it ready from scratch if object_id('tempdb..##org') is not null drop table ##org if object_id('tempdb..##hist') is not null drop table ##hist create table ##org ( id int identity , name varchar (50) , age int ) create table ##hist ( sur int identity , id int , name varchar (50) , age int , isCurrent bit , EffectiveFrom datetime , EffectiveTo datetime ) insert into ##org values ('tom','34') , ('dick','29') , ('harry','40') insert into ##hist select * , 1, GETDATE(), '9999-1-1' from ##org select * from ##org select * from ##hist */ /* Section 2: Perform an opertion. Perform an Insert, Update or Delete -- insert insert into ##org values ('ron','19') -- update update ##org set name = 'jerry' where id =1 -- delete delete from ##org where id = 3 select * from ##org select * from ##hist */ --Section 3: Run this each time an above DML is performed and the changes will be saved in the history table (##hist) insert into ##hist (id, name, age, isCurrent, effectiveFrom, effectiveTo) select id, name, age, 0 as isCurrent, effectiveFrom, effectiveTo from ( merge ##hist as target using ##org as source on source.id = target.id when MATCHED and isCurrent = 1 and exists -- UPDATE - when record exists but some other fields changed (the bleow also handles nulls) (select source.id, source.name, source. age except select target.id, target.name, target. age) then -- UPDATE - when record exists but some other fields changed UPDATE set Target.name = Source.name , Target.age = Source.age , Target.isCurrent = 1 , Target.EffectiveFrom = getdate() , Target.EffectiveTo = '9999-1-1' when NOT MATCHED BY TARGET -- INSERT -- new records exits in the source then INSERT --(id, name, age, isCurrent, EffectiveFrom, EffectiveTo) values (source.id, source.name, source.age, 1, getdate(), '9999-1-1') when NOT MATCHED BY SOURCE -- DELETE -- if a record is not found then UPDATE EffectiveTo date as getdate() adn set isCurrent to 0 then UPDATE set Target.isCurrent = 0 , Target.EffectiveTo = getdate() ------------------------------ -- Getting the previous row before the update is comminted output $ACTION ActioinOut , deleted.id, deleted.name, deleted.age, inserted.isCurrent as IsCurrent, deleted.effectiveFrom, getdate() as effectiveTo ------------------------------ ) RowBeforeMerge where RowBeforeMerge.ActioinOut = 'Update' and RowBeforeMerge.isCurrent = 1 -- this helps not to write the the row that is deleted twice -- when DELETED inserted.isCurrent is equal to 0 ; select * from ##org select * from ##hist
TSQL – History Table (Capture Change in Data)
August 18, 2017 by Usama
Leave a comment