Re: SQL question: inserting rows from a parallel databse

On 04-Apr-2012 18:57 , John McKee wrote:

<<SNIP>>
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. <<SNIP>>
<<SNIP>> 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 starting 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 that need to be inserted, and 3)
I only want to insert specific rows where a match exists between
PROVIDER_ID and PRCEDURE_ID match between the two databases.

<<SNIP>>

In describing such scenarios, the DDL is often helpful to understand the relationships; or at least definition of the referential integrity rules, regardless of just implied or actually enforced.

Recovery from data that is 'years out of date' surely must be undesirable.? Be sure to backup any logging before doing much work in that database, in case logging wraps, versus effecting a full-stop upon reaching a logging limit; see my later comments, alluding possibility of recovering row data from the logs.

Although other ways to effect the same might perform better or might be more easily understood, I think the following INSERT example should be easily enough understood to be and functional to effect: copy the child rows that exist in the mirrored copy for which no matching child key exists in the active copy of the DETAIL table, and for which there exists a /parent/ row in the active copy of the HEADER table. Performing that work as recovery however, assumes the accidentally deleted rows from the active DETAIL table included every row for each key (PROVIDER_ID, PRCEDURE_ID) that was deleted; irrelevant if that key is unique, because /every row/ is /the one row/.

insert into PREFERENCE_DETAIL as target
select * from PREFERENCE_DETAIL as mirror
where
/* ensure there is a matching parent row */
exists ( select '1' from PREFERENCE_HEADER header
where mirror.PROVIDER_ID = header.PROVIDER_ID
and mirror.PRCEDURE_ID = header.PRCEDURE_ID )
/* copy only rows for which the /key/ does not already exist */
and not exists
( select '1' from PREFERENCE_DETAIL detail
where mirror.PROVIDER_ID = detail.PROVIDER_ID
and mirror.PRCEDURE_ID = detail.PRCEDURE_ID )

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.

Investigate "logging"; for other databases, that is something like the "journaling" on the DB2 for IBM i. Try to find a way to recover the deleted rows from the log data; e.g. perhaps in a similar manner, how the UPDATE example was located.

Regards, Chuck