I'm not sure if I am directing this question to the right forum or not, but I will ask it anyway in hopes that someone may know the answer.

I am in the process of writing an application that uses SQL Server 2000 to store my data. Part of the functionality of the application is the storing of historical data for each transaction. I don't want to keep that information in the same table so I've created an INDEX table for current data and a DATA table for each subsequent 'copy' or 'history' entry.

So say that each record has the name, address, phone, notes, etc. for a customer, additionally it has a field for the date the entry was originally created and a field for the date it was modified and a field of the person who modified the entry.

Obviously every time the record is changed in order to keep a history I need to copy the data from the INDEX table to the DATA table before using the UPDATE command to apply the new changes.

Is there a way to do this using an SQL or VB.NET command without having to pull each field from the INDEX table to fill the DATA table? I've made both tables identical to each other for obvious reasons so that there is no problem duplicating the data.

Any help or tips in the right direction is greatly appreciated!

Michael