Steve, what about the SUBTRACT command?
Since it only works on tables, including temp tables, you would need to
project your view to a temp table and subtract it from the original table.
SET ERROR MESSAGE 2038 OFF
DROP TABLE Exception1
DROP TABLE Exception2
DROP TABLE Exceptions
SET ERROR MESSAGE 2038 ON
PROJECT TEMPORARY ProdViewTbl FROM ProductionView USING *
SUBTRACT ProdViewTbl FROM LocalTable FORMING Exception1
SUBTRACT LocalTable FROM ProdViewTbl FORMING Exception2
UNION Exception1 WITH Exception2 FORMING Exceptions
That should give you a table, Exceptions, with the PK and the
corresponding row from each table where there is a difference; i.e. two
rows for each PK - one row from ProdViewTbl and one row LocalTable.
Sort by the PK and compare the rows.
Drawbacks: The "Exception" tables are permanent tables and could bloat
the db after numerous runs. If you do a daily reload, NBD.
I tested the concept on two 2-column tables with 409 rows each; I don't
know how well (quickly) it might work on larger tables. Judicious
indices would help.
Your mileage may vary.
Doug
Wills, Steve wrote:
Does anyone know of a means to compare two records, in toto, for
equality||inequality?
The records have exactly the same structure, including column names and PK, and have a 1:1 relationship.
One record comes from a local table and represents the data as it was at its original INSERTion or most recent UPDATE. The other record is in a view, populated by the data as it currently exists in a production database. It might or might not have changed since the most recent refresh of the local data.
I would like to find a way to take a "management by exception" approach to by means of a record-by-record comparison between the pair of records. Then, if there is a difference, dig into the nature of the inequality.
IOW, for any PK, is #RecordInLocalTable = #RecordInProductionView? If equal, get next PK-matched pair of records. If not, then lather, rinse, repeat.
Thanks,
Steve in Memphis
