How can I insert multiple rows into oracle with a sequence value

Question

I know that I can insert multiple rows using a single statement  if I use the syntax in this answer    However  one of the values I am inserting is taken from a sequence  i e    insert into TABLE NAME  COL1 COL2  select MY SEQ nextval  some value  from dual union all select MY SEQ nextval  another value  from dual     If I try to run it  I get an ORA-02287 error  Is there any way around this  or should I just use a lot of INSERT statements   EDIT  If I have to specify column names for all other columns other than the sequence  I lose the original brevity  so it s just not worth it  In that case I ll just use multiple INSERT statements

User · Accepted Answer

This works   insert into TABLE NAME  COL1 COL2  select my seq nextval  a from  SELECT  SOME VALUE  as a FROM DUAL  UNION ALL  SELECT  ANOTHER VALUE  FROM DUAL

User · Answer

insert into TABLE NAME  COL1 COL2  WITH data AS       select  some value     x from dual     union all     select  another value  x from dual   SELECT my seq NEXTVAL  x  FROM data     I think that is what you want  but i don t have access to oracle to test it right now

User · Answer

It does not work because sequence does not work in following scenarios    In a WHERE clause In a GROUP BY or ORDER BY clause In a DISTINCT clause Along with a UNION or INTERSECT or MINUS In a sub-query    Source  http   www orafaq com wiki ORA-02287  However this does work   insert into table name              col1  col2    select my seq nextval  inner view       from  select  some value  someval             from dual           union all           select  another value  someval             from dual  inner view      Try it out   create table table name col1 varchar2 100   col2 varchar2 100     create sequence vcert my seq start with 1 increment by 1 minvalue 0   select   from  table name

User · Answer

this works and there is no need to use union all   Insert into BARCODECHANGEHISTORY  IDENTIFIER MESSAGETYPE FORMERBARCODE NEWBARCODE REPLACEMENTDATETIME OPERATORID REASON  select SEQ BARCODECHANGEHISTORY nextval  MESSAGETYPE  FORMERBARCODE  NEWBARCODE  REPLACEMENTDATETIME  OPERATORID  REASON from     SELECT      BAR  MESSAGETYPE       1234567890  FORMERBARCODE       1234567899  NEWBARCODE      to timestamp  20 07 12   DD MM RR HH24 MI SSXFF   REPLACEMENTDATETIME       PIMATD  OPERATORID       CORRECTION  REASON   FROM dual

User · Answer

insert into TABLE NAME  COL1 COL2  WITH data AS       select  some value     x from dual     union all     select  another value  x from dual   SELECT my seq NEXTVAL  x  FROM data     I think that is what you want  but i don t have access to oracle to test it right now

User · Answer

A possibility is to create a trigger on insert to add in the correct sequence number

User · Answer

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

User · Answer

insert into TABLE NAME  COL1 COL2  WITH data AS       select  some value     x from dual     union all     select  another value  x from dual   SELECT my seq NEXTVAL  x  FROM data     I think that is what you want  but i don t have access to oracle to test it right now

User · Answer

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

User · Answer

A possibility is to create a trigger on insert to add in the correct sequence number

User · Answer

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

User · Answer

It does not work because sequence does not work in following scenarios    In a WHERE clause In a GROUP BY or ORDER BY clause In a DISTINCT clause Along with a UNION or INTERSECT or MINUS In a sub-query    Source  http   www orafaq com wiki ORA-02287  However this does work   insert into table name              col1  col2    select my seq nextval  inner view       from  select  some value  someval             from dual           union all           select  another value  someval             from dual  inner view      Try it out   create table table name col1 varchar2 100   col2 varchar2 100     create sequence vcert my seq start with 1 increment by 1 minvalue 0   select   from  table name

User · Answer

insert into TABLE NAME  COL1 COL2  WITH data AS       select  some value     x from dual     union all     select  another value  x from dual   SELECT my seq NEXTVAL  x  FROM data     I think that is what you want  but i don t have access to oracle to test it right now

User · Answer

It does not work because sequence does not work in following scenarios    In a WHERE clause In a GROUP BY or ORDER BY clause In a DISTINCT clause Along with a UNION or INTERSECT or MINUS In a sub-query    Source  http   www orafaq com wiki ORA-02287  However this does work   insert into table name              col1  col2    select my seq nextval  inner view       from  select  some value  someval             from dual           union all           select  another value  someval             from dual  inner view      Try it out   create table table name col1 varchar2 100   col2 varchar2 100     create sequence vcert my seq start with 1 increment by 1 minvalue 0   select   from  table name

User · Answer

this works and there is no need to use union all   Insert into BARCODECHANGEHISTORY  IDENTIFIER MESSAGETYPE FORMERBARCODE NEWBARCODE REPLACEMENTDATETIME OPERATORID REASON  select SEQ BARCODECHANGEHISTORY nextval  MESSAGETYPE  FORMERBARCODE  NEWBARCODE  REPLACEMENTDATETIME  OPERATORID  REASON from     SELECT      BAR  MESSAGETYPE       1234567890  FORMERBARCODE       1234567899  NEWBARCODE      to timestamp  20 07 12   DD MM RR HH24 MI SSXFF   REPLACEMENTDATETIME       PIMATD  OPERATORID       CORRECTION  REASON   FROM dual

User · Answer

A possibility is to create a trigger on insert to add in the correct sequence number

User · Answer

It does not work because sequence does not work in following scenarios    In a WHERE clause In a GROUP BY or ORDER BY clause In a DISTINCT clause Along with a UNION or INTERSECT or MINUS In a sub-query    Source  http   www orafaq com wiki ORA-02287  However this does work   insert into table name              col1  col2    select my seq nextval  inner view       from  select  some value  someval             from dual           union all           select  another value  someval             from dual  inner view      Try it out   create table table name col1 varchar2 100   col2 varchar2 100     create sequence vcert my seq start with 1 increment by 1 minvalue 0   select   from  table name

User · Answer

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

User · Answer

A possibility is to create a trigger on insert to add in the correct sequence number

[sql] How can I insert multiple rows into oracle with a sequence value?

Examples related to sql

Examples related to oracle