Search code examples
sqloracle-databaseora-00928

Using "WITH AS" keywords when migrating data in SQL

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
Solution

  • 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.