Wiki MaCa

 
use test 
go 

select * from [psygis].[zorgc_stg] 
order by zorgc_iden, audit_id 

truncate table test.psygis.zorgc_hda 

insert into test.psygis.zorgc_hda 
select [audit_id],'2016-03-01 11:43:29.890','9999-12-31',[voided],[mut_dat],[mut_tyd],[mut_gbr],[zorgc_iden],[act],[oms],[zgs],[dbc] 
from [psygis].[zorgc_stg] stg 
where stg.audit_id = 2251 

UPDATE hda 
SET voided = CASE WHEN stg.audit_id IS NOT NULL THEN 0 ELSE 1 END 
, load_date_end = '2016-03-02 11:43:29.890' 
FROM  test.psygis.zorgc_hda hda 
LEFT OUTER JOIN test.psygis.zorgc_stg stg 
  ON 1=1 AND stg.audit_id = 2852 AND stg.stg_sequence = 1   
  AND ( hda.[zorgc_iden] = stg.[zorgc_iden] OR (hda.[zorgc_iden] IS NULL AND stg.[zorgc_iden] IS NULL)) 
WHERE hda.voided = 0 AND hda.load_date_end = '9999-12-31' 
AND (( hda.[mut_dat] <> stg.[mut_dat] OR ((hda.[mut_dat] IS NULL AND stg.[mut_dat] IS NOT NULL) OR (hda.[mut_dat] IS NOT NULL AND stg.[mut_dat] IS NULL))) 
OR ( hda.[mut_tyd] <> stg.[mut_tyd] OR ((hda.[mut_tyd] IS NULL AND stg.[mut_tyd] IS NOT NULL) OR (hda.[mut_tyd] IS NOT NULL AND stg.[mut_tyd] IS NULL))) 
OR ( hda.[mut_gbr] <> stg.[mut_gbr] OR ((hda.[mut_gbr] IS NULL AND stg.[mut_gbr] IS NOT NULL) OR (hda.[mut_gbr] IS NOT NULL AND stg.[mut_gbr] IS NULL))) 
OR ( hda.[act] <> stg.[act] OR ((hda.[act] IS NULL AND stg.[act] IS NOT NULL) OR (hda.[mut_gbr] IS NOT NULL AND stg.[mut_gbr] IS NULL))) 
OR ( hda.[oms] <> stg.[oms] OR ((hda.[oms] IS NULL AND stg.[oms] IS NOT NULL) OR (hda.[oms] IS NOT NULL AND stg.[oms] IS NULL))) 
OR ( hda.[zgs] <> stg.[zgs] OR ((hda.[zgs] IS NULL AND stg.[zgs] IS NOT NULL) OR (hda.[zgs] IS NOT NULL AND stg.[zgs] IS NULL))) 
OR ( hda.[dbc] <> stg.[dbc] OR ((hda.[dbc] IS NULL AND stg.[dbc] IS NOT NULL) OR (hda.[dbc] IS NOT NULL AND stg.[dbc] IS NULL)))) 
OR stg.audit_id IS NULL 

insert into test.psygis.zorgc_hda 
select  
2852,'2016-03-02 11:43:29.890','9999-12-31',stg.[voided],stg.[mut_dat],stg.[mut_tyd],stg.[mut_gbr],stg.[zorgc_iden],stg.[act],stg.[oms],stg.[zgs],stg.[dbc] 
--, case when hda.audit_id IS NULL then 'nieuw' else 'gewijzigd' end , case when hda.load_date_end = '2016-03-01 11:43:29.890' then 'gewijzigd' else 'nieuw' end  
from test.psygis.zorgc_stg stg 
LEFT OUTER JOIN test.psygis.zorgc_hda hda 
  ON 1=1  
  AND  hda.voided = 0  
  AND ( hda.[zorgc_iden] = stg.[zorgc_iden] OR (hda.[zorgc_iden] IS NULL AND stg.[zorgc_iden] IS NULL)) 
WHERE stg.audit_id = 2852 AND stg.stg_sequence = 1  
AND ( 
  hda.load_date_end = '2016-03-02 11:43:29.890' -- gewijzigde 
  OR 
  hda.audit_id IS NULL -- nieuwe 
) 

select * from test.psygis.zorgc_hda 
order by zorgc_iden, load_date