0

I am trying to copy rows from one table to another and in addition add a new primary key and system date.

INSERT into tableB (id, date, name, type) 
    SELECT id_seq.nextval, sysdate, name, substr(type, 1, 1) 
    FROM tableA 
    WHERE type != 'ABC' 
    ORDER BY name;

I get the error

sequence number not allowed here

Not sure how and where to specify the new primary key. Thanks from a total newbie in the Oracle SQL world!

----> UPDATE: Final solution

 INSERT into tableB (id, date, name, type) 
    SELECT id_seq.nextval, sysdate, name, type1 
    FROM (SELECT name, substr(type, 1, 1) as type1 
    FROM tableA 
    WHERE type != 'ABC' 
    ORDER BY name);
Veronica
  • 9
  • 3
  • You need to wrap it with subquery. From http://www.orafaq.com/wiki/ORA-02287 `The following are the cases where you can't use a sequence: For a SELECT Statement: In a WHERE clause In a GROUP BY or ORDER BY clause` – Lukasz Szozda Apr 13 '16 at 16:22
  • 1
    Thank you! Removing the ORDER BY clause fixed it! – Veronica Apr 13 '16 at 17:18
  • Update: After looking at the solution already out there, I modified my SQL to still order the data by using a sub query. – Veronica Apr 13 '16 at 19:44

0 Answers0