Loading a history table to a normalised table

Category: sql server dbdesign

Question

MrFlinstone on Wed, 15 Nov 2017 11:04:02


Hi All.

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.

Replies

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

https://www.mssqltips.com/sqlservertip/2883/using-the-sql-server-merge-statement-to-process-type-2-slowly-changing-dimensions/