insert into a table from a select query in oracle

Question

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!

what is the data type of col1, ... col5? This error has to do with conversion to date using a date format model. — user5683823
– user5683823, Commented Mar 31, 2016 at 18:27
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. — user330315
– user330315, Commented Mar 31, 2016 at 18:36
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
– Alex Poole, Commented Mar 31, 2016 at 18:36
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
– user747291, Commented Apr 1, 2016 at 12:56

APC · Accepted Answer · 2016-04-02 08:15:53Z

3

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

answered Apr 2, 2016 at 8:15

APC

147k20 gold badges180 silver badges289 bronze badges

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

Collectives™ on Stack Overflow

insert into a table from a select query in oracle

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related