[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.


More information about the thelist mailing list