Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2003
    Posts
    77

    Unanswered: insert-select sql behaviour

    Hi,

    I'm working on DB2 v7.2 on windows 2000 server.
    I'm trying to run a sql -

    insert into table tab1 (col1, col2, col3) values (Select col1, col2, col3 from tab2)

    which is not working. Gives the error
    DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
    SQL0412N Multiple columns are returned from a subquery that is allowed only
    one column. SQLSTATE=42823.

    Stragely though, if I give a sql

    insert into table tab1 (col1, col2, col3) (Select a1, a2, a3 from tab2)

    It works perfectly.

    When looking at the syntax rules, the first sql is right and this one is wrong.
    I dont know why it is behaving in this way.
    Please let me know if you've any comments on this.

    Thanks,
    -Preeti
    Last edited by preetim; 10-01-04 at 13:20.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Actually, according to the syntax rules, the first one is wrong and the second one is right.

    Here is the syntax:

    >>-INSERT INTO--+-table-name-------+---------------------------->
    +-view-name--------+
    +-nickname---------+

    >--+-----------------------+------------->
    | .-,-----------. |
    | V | |
    '-(----column-name-+--)-'

    .-,----------------------------.
    V |
    >--+-VALUES----+-+-expression-+-----------+-+----------+--------><
    | | +-NULL-------+ | |
    | | '-DEFAULT----' | |
    | | .-,--------------. | |
    | | V | | |
    | '-(----+-expression-+-+--)-' |
    | +-NULL-------+ |
    | '-DEFAULT----' |
    '-+-----------------------------------+--fullselect-'
    | .-,-----------------------. |
    | V | |
    '-WITH----common-table-expression-+-'


    The reason the first one is wrong is that you have one expression (the fullselect) returning more than one colunm. You would need something like this:

    Insert into table tab1 (col1, col2, col3) values ((Select col1 from tab2),(select col2 from tab2),(select col3 from tab2))

    The reason the second one is correct is that it follows the
    INSERT INTO tabname (columns, ...) fullselect

    HTH

    Andy

  3. #3
    Join Date
    Jun 2003
    Posts
    77
    Thanks a lot. That clears my confusion.

Posting Permissions

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