I want to make an insert using union all which has a column getting values from a sequence

Question

I tried

INSERT INTO my_test_one (rollno,name, sirname, Dept) 
(select rollno_seq.nextval,'name1','sirname1', Dept 
   FROM my_test_one_backup 
  WHERE dept = 500      
UNION ALL
 select rollno_seq.nextval,'name1','sirname1', Dept 
   FROM my_test_one_backup 
  WHERE dept = 501 );

While doing this I am getting the error

Error report:

SQL Error: ORA-02287: sequence number not allowed here
02287. 00000 - "sequence number not allowed here"

Answer 1

Don't use a UNION but a single SELECT and OR in this case:

SELECT rollno_seq.nextval,'name1','sirname1', Dept 
FROM my_test_one_backup 
WHERE dept = 500 OR dept = 501

Answer 2

Try:

INSERT INTO my_test_one 
(rollno, name, sirname, Dept)  
SELECT rollno_seq.nextval,
       name1,
       sirname1,
       dept
  FROM (select 'name1' as name1,'sirname1' as sirname1, Dept
         FROM my_test_one_backup    
        WHERE dept = 500       
       UNION ALL  
       select 'name1','sirname1', Dept     
         FROM my_test_one_backup    
        WHERE dept = 501 ); 

Edit: Better still, use an OR like CodeBrickie says or and IN statement.

WHERE dept IN (500, 501);

Edit2:

Currently you are selecting 'name1', 'sirname1' as literals so each row returned will insert the next sequence number, 'name1', 'sirname1' and whatever the value of DEPT column is.

If your table has columns called name1 and sirname1 then you'll need to remove the single quotes (and you wouldn't need the column alias either) e.g.:

INSERT INTO my_test_one 
(rollno, name, sirname, Dept)  
SELECT rollno_seq.nextval,
       name1,
       sirname1,
       dept
  FROM (select name1, sirname1, Dept
         FROM my_test_one_backup    
        WHERE dept = 500       
       UNION ALL  
       select name1, sirname1, Dept     
         FROM my_test_one_backup    
        WHERE dept = 501 ); 

Or

INSERT INTO my_test_one 
(rollno, name, sirname, Dept)  
SELECT rollno_seq.nextval,
       name1,
       sirname1,
       dept
  FROM my_test_one_backup
 WHERE dept IN (500, 501);