MrFlinstone on Wed, 15 Nov 2017 11:04:02
I have a daily feed of data that comes into my inventory database, it contains meta data information about the databases. The way I would want it to work is as follows.
- Once a record comes in, it will get loaded onto the normalised table from the staging table.
- Every database will have a startdate/enddate as part of the record.
- If a database is no longer in the staging table but in the normalised table, then it means it has been deleted and its enddate will be updated as todays date.
- if the database still remains and no property has changed, the enddate remains null
- if some of the tracked properties has changed, the previous record will have a enddate updated, and a new record inserted with a null enddate.
I am looking for a stored procedure template to help with the design above.
Thanks in advance.
Visakh16 on Wed, 15 Nov 2017 11:19:38
Its a classical SCD Type 2 processing that you're looking at
See this to understand how you can implement it using MERGE statement logic in T-SQL