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 : 1100 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

0	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

0	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

Latest Posts