Helen Borrie wrote:

> >I have 2 DBs where I need to merge the data from DB1 to DB2. I
> >don't really care what is in the records currently, but am not
> >able to clear the data before hand and then do a complete insert.
>
> Why can't you pass a DSQL as:
>
> DELETE FROM ATABLE
>
> ??
>
> Then surely it is a simple datapump operation - or don't I understand
> what you want to do?

Helen - he says he is not able to clear the data beforehand - in my own
application, the data in the second table is in use and a delete all
would cause problems.

I am able to get away just deleting the record I am currently checking,
so the datapump is set up to copy a record from 1 to 2 ( there is is a
list of tens of calling points in the detail which is why I use the pump
) also I only have to update on at a time anyway.

So my approach is ( in shorthand form )

Does record exist.
Yes - delete record in 2.
Pump record from 1 to 2.

Since you appear only to want to alter one record, then I would suggest
just a simple field by field copy, and if the record does not exist,
append a blank record before the copy. This will be quite efficient,
since you have already have the record from 1, have found the record in
2, and so are set up to copy.

The more effiecient way would be to have an SQL procedure for INSERT and
UPDATE with parameters of the fields to be copied, and then use the
appropriate procedure. Which is where the InsertSQL/EditSQL sort of come
in, but those are really for situations where the insert/edit is more
complex than a single field. But there will not be a lot in it, as
database to database transfers have to go through the client.

Helen's is right if you can put up with a delay on database 2 since any
users of database 2 will see the old copy until such time as the new one
is complete, provided that the transaction covers the delete and the
pump. They will have problems if they are trying to edit while it
happens.

So a little more info on the problem, such as why you can't delete and
less padding with code. Process first, then detail.

--
Lester Caine
-----------------------------
L.S.Caine Electronic Services