In my experience, the difficulty you're going to have is that unlike
the i, most DB's don't allow you to restore a single table to a
temporary schema easily...

You'll need to create a new DB instance to mount the mirror copy,
export the table(s) data to CSV or whatever and import it into a new
table(s) on the production DB.

Now it's simply a matter of writing an SQL that picks out the missing records.
WHERE EXISTS and/or EXCEPTION JOIN will be your friends.

HTH,
Charles

On Wed, Apr 4, 2012 at 9:57 PM, John McKee <jmmckee@xxxxxxxxxxxxxx> wrote:
First off, I apologize, since this isn't an i specific issue.  I do
not know where to turn.  This a mess tht SQL >might< be able to
address.  I just have no idea how it might be stated.

The problem:  Somebody, using an application function, deleted detail
rows from a production table named PREFERENCE_DETAIL.  The header
table, PREFERENCE_HEADER has not been damaged.  No idea when this
happened.  Obviously a failure of design.  But, it is a Windows
application and not an application that has the potential data
restriction available on the i.

I have no idea when this mess ws created, and limited database
backups.  I can't just replace the entire table from a backup anyway,
s maintenance is done by a large number of people at sporadic
intervals.  There is a mirrored copy, years out of date, that could be
used to at least fix one specific problem.  At least, it would be a
strting point.

I was directed to a website that provided what is presented below.
Problems with this are: 1) It does an update where an insert is
needed. 2) There are 20 - 30 columns tht need to be inserted, and 3) I
only want to insert specific rows where a mtch exists between
PROVIDER_ID and PRCEDURE_ID match between the two datbses.

The vendor is not going to be any help.  Politics, arrogance, and
indifference has the application eight releases out of date.  This is
worse than trying to get support for v4r5 from IBM. in my opinion.

Can the following be used in some modified form to fix, partially, this mess?

UPDATE A
 SET ControllingSalesRep = RA.SalesRepCode
from DHE.dbo.tblAccounts A
 INNER JOIN DHE_Import.dbo.tblSalesRepsAccountsLink RA
   ON A.AccountCode = RA.AccountCode

Agin, it needs to be n INSERT as rows are gone, and the above would
make the mess far worse since it is apparently going to change a
column on every row that mtches.


Worst case, the people who use this system could print the formatted
data from the old mirror and manually re-enter things - and try to
figure out what, if anything, has changed.  But, due to the sharp
knives, staff has been cut to the bone.  This system is used to
increase efficiency in the surgery department.  But, not in this case.

Again, I apologize for this non i issue.  I just know that I have seen
many great responses for i related help.  I understand if this is just
too far removed to be considered.


John McKee
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].