Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Location
    San Francisco
    Posts
    4

    Question Unanswered: Insert results of recursive query

    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

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please try:
    INSERT INTO MYSCHEMA.MY_TABLE WITH temptab(...) AS(...) SELECT * FROM temptab;

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  4. #4
    Join Date
    Apr 2009
    Location
    San Francisco
    Posts
    4

    thx

    that did it
    N

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •