copying selected column data from one table and insert into other table [Resolved]

Posted by Pallubhosale under Oracle on 3/1/2015 | Points: 10 | Views : 549 | Status : [Member] | Replies : 5
Hi All,

create table sortData(id NUMBER, MATRIC_NMBR varchar2(50));

I have created seqence for id as ID_SEQ .
while inserting record error is comming missing right paranthesis

insert into sortData(id,Matric_Nmbr)
values(ID_SEQ.NEXTVAL,
(select MATRIC_NMBR from mae_eid
order by COURSE_CODE,SEX,NATIONALITY));

Pallavi


Responses

Posted by: Bandi on: 3/3/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
this one ?

insert into sortData(id,Matric_Nmbr) 

select ID_SEQ.NEXTVAL, inner_view.MATRIC_NMBR
FROM (SELECT MATRIC_NMBR from mae_eid order by COURSE_CODE,SEX,NATIONALITY) inner_view;


I doesn't have ORacle environment with me now. So i cannot test it


NOTE: Look into the previously posted link...
From Oracle Wiki, error 02287 is


An ORA-02287 occurs when you use a sequence where it is not allowed.
Of the places where sequences can't be used, you seem to be trying:

In a sub-query
So it seems you can't do multiples in the same statement.

The solution they offer is:

If you want the sequence value to be inserted into the column for every row created, then create a before insert trigger and fetch the sequence value in the trigger and assign it to the column


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 3/3/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
try this

insert into sortData(id,Matric_Nmbr) 
select ID_SEQ.NEXTVAL, MATRIC_NMBR
from mae_eid
order by COURSE_CODE,SEX,NATIONALITY


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 3/3/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
refer
http://stackoverflow.com/questions/228221/how-can-i-insert-multiple-rows-into-oracle-with-a-sequence-value

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pallubhosale on: 3/3/2015 [Member] Starter | Points: 25

Up
0
Down
Hi Chandu,

For this query error is cumming as " Sequence no is not allowed here"
insert into sortData(id,Matric_Nmbr)
select ID_SEQ.NEXTVAL, MATRIC_NMBR
from mae_eid
order by COURSE_CODE,SEX,NATIONALITY



Pallavi

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pallubhosale on: 3/3/2015 [Member] Starter | Points: 25

Up
0
Down
In oracle we can use ronum for is identity coloumn, and by creating trigger on insert row also working
i achieved result from below query:
Thanks for reply..

insert into sortData(id,Matric_Nmbr)
select ronum, MATRIC_NMBR
from mae_eid
order by COURSE_CODE,SEX,NATIONALITY

Pallavi

Pallubhosale, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response