I need to implement a rudimentary change tracking scheme for three tables in a database that is backed up daily. Normally, I would track changes via a data entry form, but in this database some of the data is put into the tables by a 3rd party add-in to AutoCAD. Thus, some of the data entry is outside my control and ability to track it.
Because of this, I am planning to implement a change tracking scheme by comparing the data in todays database tables to those in yesterday's back up. Records that are detected to be changed from yesterday will be marked by putting today's date in a Rev_Date field.
I am looking at how to do this, and trying to work up the automatic generation of SQL code to do the comparison and marking quickly. Since the fields in the three tables of interest may change in the future, I want to generate the appropriate SQL statement on the fly so that I don't have to keep updating it. (I have done that type of thing before.)
There is a key field called IDCOUNT that I can use to join today's copy of the tables to yesterdays copy.
Where I am stumbling on this is in determining whether I can do the UPDATE statement with a reference to a table in an external db (yesterdays db copy, in this case).
It looks like I need to generate the UPDATE statement such that it does a join between today's table copy and yesterdays based on IDCOUNT. The SET clause is easy since I am only setting one field, Rev_Date. The WHERE clause will be a pain... I will need to create a list of comparisons between a lot of fields in different tables having the same names.
MY QUESTION:
I don't see anywhere where it is possible to reference a table in an external DB in an UPDATE statement. Is this possible?
If not, then I assume I should create a SELECT query on the fly to generate the relevant set of IDCOUNTS, then use that result set to feed the UPDATE query (subquery approach). Correct?
I will be happy to answer any clarifying questions before awarding points.
Thanks!
Steve
Start Free Trial
