Posts Tagged ‘history’

--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 
	, ('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
	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
						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 
		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
		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 »