2

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
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
benstpierre
  • 32,833
  • 51
  • 177
  • 288
  • Do I need to use the "CREATE TABLE something AS" syntax instead? As seen here... http://www.dba-oracle.com/t_sql99_with_clause.htm – benstpierre Nov 19 '09 at 19:24
  • I have to ask this - you are using () around the SELECT and not {} as in your question, correct? – dpbradley Nov 19 '09 at 19:53

1 Answers1

3

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.

Heinzi
  • 167,459
  • 57
  • 363
  • 519