CODEWITHSUNDEEP
oracle-databaseselectinsert
user747291
user747291

Reputation: 821

insert into a table from a select query in oracle

I am trying to insert the values of a select query which need to go into a column, into another table along with couple other columns something like below. Data types for the columns.

col1-number, col2-number, col3-varchar, col4-date, col5-char(1),col6-char(1)

insert into table1 (col1, col2, col3, col4, col5, col6, col7)
select ( uid, 22, 'HTML', sysdate,'null','null','N')
from ( select query here)

but when I run the above query I am getting an error - ORA-01858: a non-numeric character was found where a numeric was expected near line 2 of the above query. Can anyone please point me what is wrong with this or if there is a better way to do it. thank you!

Upvotes: 0

Views: 6233

Answers (1)

APC
APC

Reputation: 146349

'null' is a string. Presumably either col5 or col6 is a number column, and we cannot cast that string to a number.

To insert no value into those columns use the null keyword like this:

insert into table1 (col1, col2, col3, col4, col5, col6, col7)
select ( uid, 22, 'HTML', sysdate, null, null,'N')
from ( select query here)

Alternatively remove them from the insert projection:

insert into table1 (col1, col2, col3, col4, col7)
select ( uid, 22, 'HTML', sysdate, 'N')
from ( select query here)

Upvotes: 3

Related Questions