user3586248 user3586248 - 1 month ago 9
SQL Question

Inserting into One Field Select statement Results

I am using Oracle databases. I have an sql table

PS_Z_STAGE_TEST_JE
that has three fields (
EMPLID
,
LAST_NAME
,
FIRST_NAME
).

I am trying to do a
select
statement that will pull many EMPLIDs from sql table:
ps_vc_plan_mem
and insert them into the
EMPLID
column while leaving the other two fields (
LAST_NAME
and
FIRST_NAME
)
null
.

Below is my SQL but it will say


Cannot insert null Value into
LAST_NAME



when I try to run it.

INSERT INTO sysadm.PS_Z_STAGE_TEST_JE (EMPLID)
SELECT DISTINCT(emplid)
FROM ps_vc_plan_mem
WHERE vc_plan_id IN ('PNC-RS','PNC-SO','PNC-ESPP');

Answer

The error message is exactly what it says it is. Your last_name column must be defined as not null. Therefore, you can't insert a null into it. Since you didn't define what to insert into the column in your insert, it tries to insert null by default and fails.

You must insert something into last name. I would suggest either a default string or an empty string if you can't get an actual last name to insert.

INSERT INTO sysadm.PS_Z_STAGE_TEST_JE (EMPLID, LAST_NAME) 
SELECT DISTINCT(emplid), 'N/A'
FROM ps_vc_plan_mem 
WHERE vc_plan_id IN ('PNC-RS','PNC-SO','PNC-ESPP');

Alternatively, you could alter your table so that last_name is nullable.