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

Daniel,

just curious .. how would he ever do it using a materialized view? am i missing something? he plans to create a duplicate record in the same table ... So if he is not talking about a duplicate/snapshot of a table ... how would a mat view help?

To OP, IMHO - thats the way you would do it. I don't understand how you are visualizing #3 to happen? If you are worried about defining columns etc ... I would utilize dynamic sql or execute immediate to create my insert statements. That way you would not need to explicitly list all columns (except the id column)

Anurag

"DA Morgan" <damorgan_at_exesolutions.com> wrote in message news:3E4C2D46.F3EB0DE7_at_exesolutions.com...
> 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 Thu Feb 13 2003 - 22:48:05 CST