Forum Stats

  • 3,759,490 Users
  • 2,251,552 Discussions


Need to insert multiple values but not able to do

Albert Chao
Albert Chao Member Posts: 70 Green Ribbon
create table demo(id_num number not null,first_name varchar2(20), stage_0_5 varchar2(20), stage_10_15 varchar2(50));
insert into demo values(1,'A','0.Not Started','','');

create table main_table(id_main number not null, first_name varchar2(20), stage_code number(10));
insert into main_table(id_main,first_name) select id_num, first_name from demo;

insert into main_table(stage_code) select regexp_replace(nvl(nvl(stage_0_5,stage_5_10),stage_10_15),'[^0-9]','') as stage from demo;

Now I want to insert demo data into the main table but I am getting the below error. I know id_main is defined as not null so throwing an error but how will I be able to insert the data in one go then ?

ORA-01400: cannot insert NULL into ("SYSTEM"."MAIN_TABLE"."ID_MAIN")


Best Answer


  • mathguy
    mathguy Member Posts: 10,081 Blue Diamond

    You wouldn't get as far as you are saying you are, with the code you posted.

    The first CREATE TABLEstatement, for table DEMO, lists four columns. The first INSERT, into the same table, has five values. No can do. Immediate error which has nothing to do with the error you show.

    So, let's ignore your first INSERT statement.

    The error you report will be thrown by the last INSERT statement. In it, you insert a row in MAIN_TABLE, but you only give a value for column STAGE_CODE. This means (by definition) that all the other column values will be NULL in the inserted row. But in the definition of table MAIN_TABLE you require that ID_MAIN must be NOT NULL. Do you understand now? In every INSERT into any table with NOT NULL columns, you must always give (non-NULL) values at least for all NOT NULL columns (unless you gave a DEFAULT value when you created the table).

    Did you think that the second INSERT into MAIN_TABLE will add a value for STAGE_CODE in the same row that was already inserted (with values just for ID_MAIN and FIRST_NAME? That's not how it works. If you want to modify that row (rather than creating a new one), you should UPDATE, not INSERT.

  • Albert Chao
    Albert Chao Member Posts: 70 Green Ribbon
    Accepted Answer

    @mathguy After posting this question I was playing around with the code and found that we can write subquery to select column into one insert statement. And that worked for me. Thanks