aaa-322853 (12/30/2010)
Oops my mistake, didn't read your original post correctly!Typically when you have a type 2 you would identify this by comparing to you current record, in your case Version = 0, and then expire it before inserting the incoming record. How do you identify the next version number when expiring your outgoing record? Do you do anything clever here?
😀 -- no problem, the same happens to me all the time.
The beauty of SCD is that the number of changes per ETL cycle is a very small fraction of ETL's total volume so, solution doesn't have to be fantastic, just functional.
When a SCD change happens I read version=0; insert it as version=max(version)+1 including ToTimeStamp; then apply change to version=0 including FromTimeStamp.
Thank you for asking.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.