Copying a record in SQL to another table

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

You should be able to execute a statement like this:

Quote:

---

INSERT INTO DATA SELECT * FROM INDEX WHERE UniqueIdFieldNameHere = UniqueIdHere

---

Regards,

- Aaron.

In sql you want to create a trigger on the table which gets updated, set the trigger on update to insert the ID into the other table.

Quote:

---

Originally Posted by Aaron Young

You should be able to execute a statement like this:Regards,

- Aaron.

---

Cool, thank you very much, this worked perfectly.

Michael

peace be with you

how i can make "execute" using "adodc1" and "ado dll"

thanks