Results 1 to 9 of 9
  1. #1
    Join Date
    May 2012
    Posts
    155

    Unanswered: WITH AS doesnt work with INSERT INTO ?

    Hallo everyone,

    as simple as the title, I wonder why this code doesnt work.

    Code:
    WITH
        myBasic AS
        (
            SELECT
            	test_column1,
                 test_column2  
            FROM
                mySchema.myTable
            FETCH
                FIRST 1000 rows only)
    
    insert into mySchema.test(test_column1, test_column2)
    select * from myBasic;
    I have already created the table mySchema.test
    The error:
    Code:
    Error Code: -104, SQL State: 42601]  An unexpected token "mySchema" was found following "s only)
    It must be possible to insert table from a "with as table"?

    Thank you everyone.

    Regards,
    Ratna

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ratnalein View Post

    It must be possible to insert table from a "with as table"?
    It is possible, if you follow the syntax described in the manual.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Hint: An INSERT statement always starts with INSERT.

    Andy

  4. #4
    Join Date
    May 2012
    Posts
    155
    Hallo everyone,

    I really dont get it. I have followed the syntax correctly. My code has "INSERT":

    Code:
    insert into mySchema.test(test_column1, test_column2)
    select * from myBasic;
    Please give me a hint.. Thank you.

    Regards,
    Ratna

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Obviously you are doing something incorrectly, but it is impossible for any of us to tell you what it is because you do not give us anything to go on. What exactly did you try in its entirety? What error messages are you getting? What Db2 version and OS are you using?

    Andy

  6. #6
    Join Date
    May 2012
    Posts
    155
    Hallo Andy,

    at fist, I successfully run this:

    Code:
    WITH
        myBasic AS
        (
            SELECT
            	test_column1,
                 test_column2  
            FROM
                mySchema.myTable
            FETCH
                FIRST 1000 rows only)
    
    select * from  session.myBasic
    And that I thought, I would like to try to "INSERT INTO" my basic table "myBasic" with INSERT INTO command into an already created table. Thus, I run the ddl of the talbe mySchema.test. It worked fine.

    Finally, I run this INSERT INTO command:

    Code:
    insert into mySchema.test(test_column1, test_column2)
    select * from myBasic;
    In fact, I run it completely from the beginning:

    Code:
    WITH
        myBasic AS
        (
            SELECT
            	test_column1,
                 test_column2  
            FROM
                mySchema.myTable
            FETCH
                FIRST 1000 rows only)
    
    insert into mySchema.test(test_column1, test_column2)
    select * from myBasic;
    That did not work. I am wondering, where I made my syntax error. I have checked in the manual. My code is fine for me. That is why I asked the forum. Thank you.

    Regards,
    Ratna

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Read my first hint.

    Andy

  8. #8
    Join Date
    May 2012
    Posts
    155
    Hallo Andy,

    I got it. It must start with INSERT INTO.. Thank you..

    Regards,
    Ratna

  9. #9
    Join Date
    Oct 2012
    Posts
    1
    Dear all

    if you want to using insert records with with()

    Sample:
    insert into target-table(col1,col2)
    with tmp(col1,col2) as
    (select col1,col2 from source-table )
    select col1,col2 from tmp
    ;

Posting Permissions

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