Handling cannot insert Null into in Oracle

Question

I have an insert statement where in i am inserting the data from table_abc to table_job. Now there is mandatory column in effec_start_date in table_job, but this effec_start_date is not mandatory in table_Abc. Hence there may be null values too in effec_start_date of table_abc. While inserting from table_abc to table_job it is going into exception stating

-1400 - ORA-01400: cannot insert NULL into ("HR"."table_job"."EFF_START_DATE")

and obviously when querying table_job it is not returning any value

Is there a possibility to insert the not null values for effective_Start_date in table_job and for only null values it goes into exception

insert into table_job
(job_code,
eff_start_date,
eff_end_date,
config_id
)

select * from table_Abc;

Do you want to use a default value when the ABC value is null; or exclude those rows from the insert; or log the errors you get? Not clear if you actually want/need the errors. — Alex Poole
– Alex Poole, Commented Feb 22, 2016 at 8:42

Ricardo Arnold · Accepted Answer · 2016-02-22 08:42:28Z

3

you need to filter your data in the select statement

insert into table_job
(job_code,
eff_start_date,
eff_end_date,
config_id
)
select * from table_Abc
where eff_start_date is not null;

answered Feb 22, 2016 at 8:42

Ricardo Arnold

9331 gold badge12 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Handling cannot insert Null into in Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related