--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
Read Full Post »
