Re: Exception Reporting - Best Practices

From: songie D (songie_at_d.com)
Date: 05/21/04 

Date: Sat, 22 May 2004 00:05:19 +0100

Have an upload table, then have a SP that 'cleans' the uploaded
data once in SQL server and basically puts it in your required
format, using temp tables if necessary.
Then, figure out an SQL statement that will give you the exceptions,
such as by using NOT IN or NOT EXISTS
on the primary key column(s), for instance.
To upload an Excel file, I'd personally recommend using automation
to save it off as a CSV file, then BCPing it, although use DTS if
you must - but watch the range names (you might get spurious rows
of nulls at the end depending on how the data is entered).

"T.A. Brooks" <usna91@msn.com> wrote in message
news:eR8nO43PEHA.3044@TK2MSFTNGP10.phx.gbl...
> 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
>
>
>