Exception Reporting - Best Practices

From: T.A. Brooks (usna91_at_msn.com)
Date: 05/21/04 

Date: Fri, 21 May 2004 14:53:29 -0700

All,

I have an implementation / best practices question I thought I'd offer up to
the SQL Server gods.

I've developed a SQL Server back-ended web-based (ASP.Net) app to perform a
basic business function at work. Some of the data (look-up stuff) in my
database has an external source and it can often change or have errors
because of our external partner(s).

I've been asked to add a capability to do some exception reporting to look
at updates to the external portion before we commit them. Basically a table
comparison.

- What records are in our version but not theirs?
- What are in theirs but not ours?
- Which are in both but different?

The data comes in an Excel or comma delimited file. And it isn't completely
normalized -- ie it may have states by two letter identifier whereas in my
main table I have a state id look-up -- basically there'll have to be joins
before comparing the tables. Oh...and there'll be on average fewer than
1,000 records and fewer than 10 columns on an average comparison.

The goal is agree on a standard file that'll have the updates and that
someone can basically push a button to upload the update and generate some
form of report -- but not to 'push' the updates. Basically, at least for
now, the plan is for someone to verify the changes and then do the updates
using the actual tool (ie no batch update -- just a comparison)

So for my question(s). How would you recommend implementing something like
this?
- Should I build something to upload a file to the database and then use
temp tables, DTS and sprocs to generate the differences?
- Should I/can I generate an XML file from the master and then use
diffgrams? (I haven't yet had a good excuse to use SQLXML)
- Is there another approach I'm not seeing? Am I making this harder than it
is?
- Is there a standard approach to this sort of thing?

I'd appreciate any advice anyone can offer. Especially if you've been down
this road yourself. I don't really need coding or dba help -- i'm pretty
solid there. I'm just kicking around what the best approach might be -- and
I haven't implemented this sort of functionality before. I'm more of a DML
than a DDL person.... ; )

Thanks and cheers,
tim