Re: Insert Statement

"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

> >

> >

>

>