SQL & PL/SQL

i have a table emp1.

create table emp1 (empid number(5), ename varchar2(50),deptno number(5), designation varchar2(50),sal number(10),loc varchar2(50))

insert into emp1 values(1,458956,'john',10,'Clerk',1000,'London')

insert into emp1 values(2,212854,'Joseph',20,'CA',7000,'USA')

insert into emp1 values(3,100247,'Arjun',30,'Manager',10000,'India')

insert into emp1 values(4,326856,'Jim',40,''Senior Clerk',6000,'China')

Emp1:-

Emp2:-

create table emp2 (empid number(5), ename varchar2(50),deptno number(5), designation varchar2(50),sal number(10).,loc varchar2(50))

insert into emp2 values(101,458956,'john',10,'Clerk',1000,'London')

insert into emp2 values(102,212854,'Joseph',20,'CA',7000,'USA')

insert into emp2 values(103,100247,'Arjun',30,'Manager',10000,'India')

insert into emp2 values(104,326856,'Jim',40,''Senior Clerk',6000,'China')

insert into emp2 values(105,203323,'Catherine',40,''Senior Clerk',6000,'China')

insert into emp2 values(106,152830','Mario',60,'BDM',12000,'Canada')

insert into emp2 values(107,200674,'George',10,'SSE',7000,'Japan')

Now my requirement is to compare the emp1 and emp2 tables and generate the sequence number for table emp2 as sequence number is not assigned for the empids 203323, 152830,200674 and output the data in new table emp_output.

Note:- seq_number should be generated in considering the max value of emp2 and then seq_number should be generated as show in required output format.

emp_output table.

create table emp_output(seq_number number(5), empid number(5), ename varchar2(50), loc varchar2(50),status varchar2(50))

Required o/p;-

I tried the comparion of data using union all , the expected results are coming but am unable to generate the seq_number for emp2 table. Can anyone look into this.

Message was edited by: 3648470 removed the sequence from emp1 i.e create sequence seq_new start with 100 increment by 1;