I need a bit of help developing a method to arcive records to a history table (SQL/VB.NET). I currently have a patients table linked to a patient detail table by the subjectid field. The patient detail table will be cleared and made ready for new data on an annual basis.
I need to be able to copy records from the patient detail table to the history table and the history table contain all years detail records for the patient so you may view the patients long term history.
I have created a duplicate history table, but not sure of the best way to handle inserting and updating changes to the patient detail table into the history table.
Any ideas would be appreciated.