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

Anurag Varma wrote:

> 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
> >

I didn't catch the "same table" part. But if that's it ... why? Hasn't he heard of normalization? Doing so would make absolutely no sense in a relational database ... heck it would make no sense in a materialized view or even in 3x5 cards.

Perhaps I should have read more carefully.

Daniel Morgan Received on Thu Feb 13 2003 - 23:37:10 CST