Insert results of recursive query

NMadson · #1 (**permalink**) 04-04-09, 11:45

I'm trying to save the results of a recursive query into a table.

my recustive table = temptab

INSERT INTO MYSCHEMA.MY_TABLE
SELECT * FROM TEMPTAB

I get an error message "unexpected token: insert".

I also tried creating a view and creating a table based on the select statement; all give me the same message about an "unexpected token."

Any ideas?

Nolan

tonkuma · #2 (**permalink**) 04-04-09, 12:14

Please try:
INSERT INTO MYSCHEMA.MY_TABLE WITH temptab(...) AS(...) SELECT * FROM temptab;

tonkuma · #3 (**permalink**) 04-04-09, 12:19

An example:

Code:

------------------------------ Commands Entered ------------------------------
CREATE TABLE my_table
(id INTEGER);
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
INSERT INTO my_table
WITH temptab(id) AS (
VALUES 1
UNION ALL
SELECT id + 1
  FROM temptab
 WHERE id < 5
)
SELECT * FROM temptab
;
------------------------------------------------------------------------------
DB20000I  The SQL command completed successfully.

------------------------------ Commands Entered ------------------------------
SELECT * FROM my_table;
------------------------------------------------------------------------------

ID         
-----------
          1
          2
          3
          4
          5

  5 record(s) selected.

NMadson · #4 (**permalink**) 04-04-09, 12:59

that did it
N

Internet Services

Web Development

Digital Marketing

Consumer Tech