Re: subselect and autonumber

In article <bcfec966.0401140231.222a3628_at_posting.google.com>, rsj_at_gisportalen.dk says...
> 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
>

A sequence's nextval has to be SELECTed into something:

Insert into table 1 (auto1, col1, col2, col3) (Select seq1.nextval, colA, colB, colC from table 2 where colA = xxx));

Also note that if you want to select a nextval in your subquery, then you cannot use an ORDER BY in that subquery. This will fail:

Insert into table 1 (auto1, col1, col2, col3) (Select seq1.nextval, colA, colB, colC from table 2 where colA = xxx order by colB));

• Tom