[thelist] SQL: finding "new" records in a table
Judah McAuley
judah at wiredotter.com
Tue Sep 4 13:08:18 CDT 2007
Matt Warden wrote:
> I realize I did not answer part of your question, which is how to
> identify changed records. See below:
>
> On 9/4/07, Matt Warden <mwarden at gmail.com> wrote:
>> 4) use a sproc or load tool to update changed records (update strategy)
>
> Lacking a date_last_modified or similar tracking mechanism (you should
> beat the original developers with a club, by the way), there is not
> much you can do except find records where stage.fieldX != prod.fieldX
> for fieldX = {each of your fields}
In this particular case, the original developers are third-party
software manufacturers that don't provide api's to their software.
Consequently, I'm writing all the sql to grab data from undocumented
databases (sql server, mysql and soon oracle) and massage it into a
standardized format that we can import into our system. Fun fun.
I was afraid this was the route I'd have to go. Not the end of the
world, of course, but I try to look for elegant methods before resorting
to brute force.
> If one of the updates that can happen is a foreign key, well then
> there are some more things you will need to consider, especially if RI
> is turned off. But I will not go down that road unless you indicate it
> is necessary...
None of the updates are on foriegn keys and at this point, none of the
changes would need to cascade to other tables. Certainly a point to
consider though and thank you for bringing it up.
Cheers,
Judah
More information about the thelist
mailing list