[thelist] SQL: finding "new" records in a table

[Matt Warden]

On 9/4/07, Luther, Ron <Ron.Luther at hp.com> wrote:
> Idea - How about a 'delsert' methodology?  If your csv files are
> complete appointment records for each individual office then, upon
> receipt, you can delete all records for that office and insert all of
> the new records.  Probably faster than any search & update rigamarole.
> Would handle cancellations as well.  Might wanna save off a prior gen
> (or use one of those 'deleted' flags or timestamps with an out-of-cycle
> cleanup process) to allow you to revert data if you get a "short" file
> from some office some day.

This can get hairy, especially if there are foreign keys involved,
which there will usually be unless this is literally just a straight
replication of the data (doubt it -- sounds like an integration point
between systems). Also, you have to be very careful not to create side
effects. Does the target system track a creation date or last
modification date? If so, pretty much any strategy that does not
replicate the actual insert and update actions themselves (described
in a previous email) will have problematic side effects. Basically, if
there is any additional related data at all in the target data source
(either in the same table or in related tables), it can get very
tricky.

And if there *isn't* any additional related data, then either this
sync has questionable value, or the target tables are just a reference
for the second system, in which case truncating the table and
recreating it based on the CSV is absolutely the best approach.

-- 
Matt Warden
Cincinnati, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.