Copy record where there are additonal fields.

[swalters]

Is there a fast and clean way to copy a row from one table to another where the destination table has additional fields.

Eg,

src_table.
KEY, BLAH, BLAH2

dest_table
archive_date, archive_reason, KEY, BLAH, BLAH2

Thanks.

[hrishy]

Hi Swalters

The fastest would be to disable the indexes (mark them unusable) and do this in
a SINGLE insert:

insert /*+ append */ into dtable (est_KEY, BLAH, BLAH2)
select KEY, BLAH, BLAH2 from src_table
commit;

commit;

If you dont say another commit you will get the follwoing error

ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

when you say select * from dtable

and rebuild the indexes using UNRECOVERABLE (and maybe even parallel).

and also dont forget to use a large roll-back segment for this ;-D

[Edited by hrishy on 07-25-2002 at 02:53 AM]

[SANJAY_G]

Another way...

EXPORT with QUERY option and IMPORT.
If you are on 8i or above.

Sanjay