Hi all,
I have an issue with column datatype.Can you please look at this issue.
I have two tables emp1 and emp2 with empno column in both tables
in table emp1 column empno is nvarchar2 and in table emp2 column empno is number datatype
I am trying to insert data from emp1 to emp2 using below query and its throwing invalid number error
`insert into emp2 (empno,deptname,ename) select empno,deptname,ename from emp1;`
i tried adding to_number to above code but still no luck
`insert into emp2 (empno,deptname,ename) select to_number(empno),deptname,ename from emp1;`
can anyone please review and let me know what am i missing.

My table scripts for both tables are:

CREATE TABLE XXTEST.EMP1
(
EMPNO NVARCHAR2(4) NOT NULL,
ENAME NVARCHAR2(10 BYTE),
DEPTNAME NVARCHAR2(10 BYTE)
)

CREATE TABLE XXTEST.EMP2
(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10 BYTE),
DEPTNAME VARCHAR2(10 BYTE)
)

create table T ( x nvarchar2(2));

insert into t values ('a1');
insert into t values ('b1');
select * from t
where  not regexp_like (x, '^[0-9]*$');

X
--
a1
b1