Re: subselect and autonumber

<rsj_at_gisportalen.dk> a écrit dans le message de news:bcfec966.0401140231.222a3628_at_posting.google.com...
> Hi!
>
> I have an Oracle database insert problem! I have a table that I want
> to populate with records from another table. I use a subselect to do
> that, f.ex.:
>
> Insert into table 1 (col1, col2, col3) Select colA, colB, colC from
> table 2 where colA = xxx;
>
> This works fine! But now I want to have a column in table 1 with auto
> number. I have created a sequence seq1, and I imagine that the sql now
> should look like this:
>
> Insert into table 1 (auto1, col1, col2, col3) Values (seq1.nextval,
> (Select colA, colB, colC from table 2 where colA = xxx));
>
> But this does not work! Can anyone help me, perhaps with a "correct"
> sql?
>
> Regards
>
> RSJ
SQL> create table t (colauto number, name varchar2(30));

Table créée.

SQL> create sequence seq ;

Séquence créée.

SQL> insert into t (select seq.nextval, object_name from dba_objects where rownum<10);

9 ligne(s) créée(s).

SQL> select * from t;

   COLAUTO NAME

---------- ------------------------------
         1 ACCESS$
         2 ALL_ALL_TABLES
         3 ALL_ARGUMENTS
         4 ALL_ASSOCIATIONS
         5 ALL_CATALOG
         6 ALL_CLUSTERS
         7 ALL_CLUSTER_HASH_EXPRESSIONS
         8 ALL_COLL_TYPES
         9 ALL_COL_COMMENTS

9 ligne(s) sélectionnée(s).

Regards
Michel Cadot Received on Wed Jan 14 2004 - 04:56:12 CST