Re: copying 1 record - along with all its atribute from other table

S P Arif Sahari Wibowo wrote:

> Hi!
>
> I have this problem of making duplicate of one record in a main table,
> along with making duplicate of all the records in atribute tables linked
> to the original record.
>
> The structure is as such: there is one main table and several atribute
> tables, where multiple records in atribute tables is associated with one
> record in main table (using main table's primary key).
>
> The obvious solution is having multiple statements like this:
>
> insert into <tablenameN>
> select :newid, <field2>,<field3>...
> from <tablenameN> where id=:oldid
>
> one for each table.
> The :newid defined to the new key value for the new record from main
> table.
>
> My questions:
>
> 1. Are there better or more elegant solutions than this?
>
> 2. Are there any solutions that doesn't need me defined all the column (so
> it still work if I add or delete columns)?
>
> 3. Should I worry about somebody modify the data between those multiple
> insert-select (of copying single record and its atributes), and resulting
> inconsistent duplicates?
>
> Thank you.
>
> --
> S P Arif Sahari Wibowo
> _____ _____ _____ _____
> /____ /____/ /____/ /____ arifsaha_at_yahoo.com
> _____/ / / / _____/ http://www.arifsaha.com/

You should worry and there is no other way unless you do it with a materialized view which would be my recommendation.

Daniel Morgan Received on Fri Feb 14 2003 - 00:41:58 CET