Using "WITH AS" keywords when migrating data in SQL...

Question

Is it possible to do this in SQL?

If I remove the INSERT statement the SELECT works, if I have the insert Oracle complains that "missing SELECT keyword".

WITH tmpdata AS
(
//SOME arbitrary select statement
)

INSERT INTO myTable (someId, somevalue, someothervalue)
SELECT
 mysequence.nextval,
 tmpData.somevalue,
 tmpData.someothervalue,
FROM
 tmpdata,
 sometabletojoin
WHERE
 tmpdata.somevalue = sometabletojoin.somevaluebutintheothertable

Answer 1

This should work:

INSERT INTO myTable (someId, somevalue, someothervalue)
WITH tmpdata AS
(
   ...
)
SELECT ...

Explanation: WITH and SELECT belong together; they are part of the same query. Therefore, in your example, Oracle complains about the "missing SELECT" when it reaches the (unexpected) INSERT after parsing the WITH clause.