CODEWITHSUNDEEP
sqlsql-serversql-server-2014
SabVenkat
SabVenkat

Reputation: 68

Taking snapshot of SQL tables

I have a set of referential tables with different schema which we use as a reference data during integration of files. The reference data can be modified from the GUI.

And the requirement is, I need to create a snapshot of data if there are any changes. For eg., Users should be able to see which referential data has been used for particular date.

Option 1: Historize all the tables over night everyday with date. This way when users want to see the data used for particular date, we can easily query the corresponding history table. As users doesnt change the data everyday, this way we will make the database bigger day by day.

Option 2: Historize only the data(rows) which has been modified with modified date and use the view to fetch the data for particular days. But this way I need to write many views as the schema is different for different tables.

If you know of the best way I can use, I would appreciate it if you share your knowledge.

Thanks,

Upvotes: 1

Views: 1463

Answers (1)

Sergio.Gaspar
Sergio.Gaspar

Reputation: 66

Not sure if possible but:
Option 3: Create/Edit triggers OnInsert/Update/Delete to write new values to an "historical table" and include a timestamp.
To get the Admin data used on day "X" just use the timestamp.
Another option (again not sure if possible) is to add "start_dt/end_dt" to the admin tables and have the processes lookup only the active data
Sérgio

Upvotes: 1

Related Questions