insert into a table from a select query in oracle

0
votes

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!

1
oracleselectinsert
what is the data type of col1, ... col5? This error has to do with conversion to date using a date format model. – mathguy
Please edit your question and add the complete CREATE TABLE statement for table1. Unrelated, but: the parentheses around the column list in the select part are totally useless. – a_horse_with_no_name
Presumably col1 is a date, and the subquery you haven't shown is returning a string which can't be implicitly converted to a date. You've changed and hidden so much though it's hard to tell what you're really doing. You have too many parentheses at the moment, for a start, which would cause ORA-00907 with what you've shown. It would be much easier if you showed your actual query and the table definition. – Alex Poole
Or col4 is of CHARtype :) – J. Chomel
Added the columns types on the question. The subquery is returning numbers, which need to be inserted in col1. hope this makes sense. thanks – user747291

1 Answers

3
votes

'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)