SQL & PL/SQL

Hi all,

I need to copy dat from table A to table B, and - of course - referential intaigrity should be maintained.
An additional problem is that the data may partly already be in the target table (possibly with different keys!!), so it should not be inserted again ... For the new data, that is not yet in the target table, a sequence number has to be used.

My first idea was to use negative id numbers for the to-be-copied data ... Like this, the new and old data would just be in the table, next to each other. But this plan was rejected.

So, I had the following scenario in mind :

1. disable constrants
2. copy new data from source to target using negative IDs
3.create for each ID a "mapping table" (mapping between old, turned negative ID and new sequence number
4. update the IDs with the data from the mapping tables
5. enable constraints again

Now, this solution is not 100% safe as in some case referential integrity may not be assured (e.g. data that was not copied, because it was not new, but that had a different ID in source and target, linking could possible be wrong in target, or PK to wich a FK is referring may even be missing).

But maybe there is a much more straight-forward solution ???
Any suggestions are welcome!

thnx.

Greetings,