Re: Insert Statement

Thanks for giving the time to answer this question. I must of had a bad day. The following worked when I got home

insert into b select * from a;

Thanks again

regards

Tony

"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:3f4e7950$0$259$cc9e4d1f_at_news.dial.pipex.com...
> "Tony Rees" <tony_rees_at_stratexnet.com> wrote in message
> news:bikv5k$283$1$8302bc10_at_news.demon.co.uk...
> > Hi Niall
> >
> > I realize this and it states that you can only insert one row. Perhaps I
> > misread the manual.
>
> Hmm I see what you mean the docs say
>
> In a single-table insert, you insert values into one row of a table, view,
> or materialized view by specifying values explicitly or by retrieving the
> values through a subquery
>
> which is somewhat misleading since the subquery can return many rows, the
> only restriction (and it seems you have hit this) is
>
>
> If you retrieve values through a subquery, then the select list of the
> subquery must have the same number of columns as the column list of the
> INSERT statement. If you omit the column list, then the subquery must
> provide values for every column in the table.
>
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
> ******************************************
>
> --
> Niall Litchfield
> Oracle DBA
> Audit Commission UK
> *****************************************
> Please include version and platform
> and SQL where applicable
> It makes life easier and increases the
> likelihood of a good answer
> ******************************************
>
>
>
> I am trying to create an archive process for a reporting
> > warehouse an require to copy the contents of the archive table A into
> table
> > B without removing the table
> >
> > thanks anyway
> >
> > Tony
> >
> > "Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in
message
> > news:3f4dfc1b$0$245$ed9e5944_at_reading.news.pipex.net...
> > > Tony Rees" <tony_rees_at_stratexnet.com> wrote in message
> > > news:biktm6$ut$1$8300dec7_at_news.demon.co.uk...
> > > > Dear All,
> > > >
> > > > Can anyone provide the insert statement for me to allow me to copy
> data
> > > from
> > > > table A to table B.
> > > >
> > > > ie select into A as select * from B (or something similar)
> > > >
> > > > I know that I can do the following
> > > >
> > > > create table B as select * from A
> > > >
> > > > but this is not what I want to do. Both tables have exactly the
same
> > > > structure. Table B is truncated as part of the process thus hanging
> on
> > to
> > > > it's indexs and other objects
> > >
> > > The insert statement is documented in the sql reference which is part
of
> > the
> > > oracle docs available at tahiti.oracle.com
> > >
> > >
> > > --
> > > Niall Litchfield
> > > Oracle DBA
> > > Audit Commission UK
> > >
> > >
> >
> >
>
>
Received on Thu Aug 28 2003 - 18:08:08 CDT