Insert multiple rows with multiple values nextval Oracle SQL

Question

I'm trying to insert 2 rows with several values and test_id.nextval into an existing dataframe without including the column headings in the code:

insert into x
    select * from
        (select 12345, 'text', test_id_seq.nextval from dual
    union all
        select 23589, 'other text', test_id_seq.nextval from dual);

I got the error: sequence number not allowed here. So I removed the sequence number. Then the error not enough values occured. How can I insert multiple rows into an existing table with nextval ids?

score 2 · Accepted Answer · answered Apr 27 '17 at 16:23

2

Try this:

    insert into x
    select tt.* , test_id_seq.nextval from
    (select 12345, 'text' from dual
    union all
    select 23589, 'other text' from dual) tt;

answered Apr 27 '17 at 16:23

Oto Shavadze

40,603
55
152
236

At first your code worked, but after that I added different values it didn't work anymore. `insert into a_glw select tt.*, work_id_seq.nextval from (select 11111, 'text', 'text', 'text', NULL, 'text', 'text', 'text', '?', 'text' from dual union all select 11111, 'text', 'text', 'text', NULL, 'text', NULL, NULL, '?', 'text' from dual) tt;` produces error `00918. 00000 - "column ambiguously defined` referring to the first line. – yPennylane May 02 '17 at 16:31
@yPennylane - try to set aliases for columns, like this: `insert into a_glw select tt.*, work_id_seq.nextval from (select 11111 col1, 'text' col2, 'text' col3, 'text' col4, NULL col5, 'text' col6, 'text' col7, 'text' col8, '?' col9, 'text' col10 from dual union all select 11111, 'text', 'text', 'text', NULL, 'text', NULL, NULL, '?', 'text' from dual) tt` – Oto Shavadze May 02 '17 at 16:57
I found out, that I'm not allowed to put the same text in 2 columns of one select statement. I don't understand why. – yPennylane May 02 '17 at 16:59
it doesn't work. Now the error `01722. 00000 - "invalid number"` occurs. – yPennylane May 02 '17 at 17:01
So what would be the solution? – yPennylane May 02 '17 at 17:07
`"invalid number"`, this looks like `a_glw` table column types problem – Oto Shavadze May 02 '17 at 17:07
but it works, when all values of one select statement are different. Then 2 lines are inserted. – yPennylane May 02 '17 at 17:09
looks like, that you have numeric column type, and trying to insert non numeric value inside. Are you sure you not doing this? – Oto Shavadze May 02 '17 at 17:14
The 1st, 3rd and last column are number format and the rest is varchar2(4000 byte). The error occurs when I change a varchar2 column from `text` to `?` while there's another `?` in the first select statement. I know that I put text into a number format column, but the error shows up only when I change from `text` to `?` – yPennylane May 02 '17 at 17:19
I think better would be ask another question, in which you will be provide table structure and example `insert` statement. – Oto Shavadze May 02 '17 at 17:24
I posted this question here: https://stackoverflow.com/questions/43743968/insert-into-with-union-all-and-nextval-doesnt-work-with-duplicate-values – yPennylane May 02 '17 at 17:34

Insert multiple rows with multiple values nextval Oracle SQL

1 Answers1