Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2013
    Posts
    2

    Unanswered: inserting declared temp table data in main table.

    is there any way i can insert the temporary declared table data into table?
    I want to do something like below

    INSERT INTO AVS.CIMAP_MODGI (I_MOD, N_CNTRY, N_MAKE, N_MOD, N_BDYSTYLE, N_SEG) values(
    with temp as(
    select
    ROW_NUMBER() OVER(PARTITION BY N_CNTRY, N_MAKE,N_MODEL ,N_BDY_STYLE ORDER BY
    N_CNTRY, N_MAKE,N_MODEL ,N_BDY_STYLE,i_mod) as rownumb
    , N_CNTRY, N_MAKE,N_MODEL ,N_BDY_STYLE ,N_SEG,i_mod
    from CIMAP_MODFEED ) select i_mod,trim(N_CNTRY),
    trim(N_MAKE),trim(N_MODEL) ,trim(N_BDY_STYLE) ,trim(N_SEG) from temp where rownumb=1
    order by i_mod )
    with ur



    [temp as(
    select
    ROW_NUMBER() OVER(PARTITION BY N_CNTRY, N_MAKE,N_MODEL ,N_BDY_STYLE ORDER BY
    N_CNTRY, N_MAKE,N_MODEL ,N_BDY_STYLE,i_mod) as rownumb
    , N_CNTRY, N_MAKE,N_MODEL ,N_BDY_STYLE ,N_SEG,i_mod
    from CIMAP_MODFEED ) select i_mod,trim(N_CNTRY),
    trim(N_MAKE),trim(N_MODEL) ,trim(N_BDY_STYLE) ,trim(N_SEG) from temp where rownumb=1
    order by i_mod]
    this runs fine. want to insert its results in main table.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see the Syntax of the INSERT statement.

    INSERT - IBM DB2 9.7 for Linux, UNIX, and Windows
    Code:
    Syntax
    
    >>-INSERT INTO--+-table-name-------+---------------------------->
                    +-view-name--------+   
                    +-nickname---------+   
                    '-(--fullselect--)-'   
    
    >--+-----------------------+--+---------------------+----------->
       |    .-,-----------.    |  '-| include-columns |-'   
       |    V             |    |                            
       '-(----column-name-+--)-'                            
    
                 .-,----------------------------.              
                 V                              |              
    >--+-VALUES----+-+-expression-+-----------+-+----------+-------->
       |           | +-NULL-------+           |            |   
       |           | '-DEFAULT----'           |            |   
       |           |    .-,--------------.    |            |   
       |           |    V                |    |            |   
       |           +-(----+-expression-+-+--)-+            |   
       |           |      +-NULL-------+      |            |   
       |           |      '-DEFAULT----'      |            |   
       |           '-row-expression-----------'            |   
       '-+-----------------------------------+--fullselect-' 
         |       .-,-----------------------. |                 
         |       V                         | |                 
         '-WITH----common-table-expression-+-'                
    
    >--+--------------+--------------------------------------------><
       '-WITH--+-RR-+-'   
               +-RS-+     
               +-CS-+     
               '-UR-'

  3. #3
    Join Date
    Jan 2013
    Posts
    2
    you are an I was just fool, dint use syntax properly initially.



    Quote Originally Posted by tonkuma View Post
    Please see the Syntax of the INSERT statement.

    INSERT - IBM DB2 9.7 for Linux, UNIX, and Windows
    Code:
    Syntax
    
    >>-INSERT INTO--+-table-name-------+---------------------------->
                    +-view-name--------+   
                    +-nickname---------+   
                    '-(--fullselect--)-'   
    
    >--+-----------------------+--+---------------------+----------->
       |    .-,-----------.    |  '-| include-columns |-'   
       |    V             |    |                            
       '-(----column-name-+--)-'                            
    
                 .-,----------------------------.              
                 V                              |              
    >--+-VALUES----+-+-expression-+-----------+-+----------+-------->
       |           | +-NULL-------+           |            |   
       |           | '-DEFAULT----'           |            |   
       |           |    .-,--------------.    |            |   
       |           |    V                |    |            |   
       |           +-(----+-expression-+-+--)-+            |   
       |           |      +-NULL-------+      |            |   
       |           |      '-DEFAULT----'      |            |   
       |           '-row-expression-----------'            |   
       '-+-----------------------------------+--fullselect-' 
         |       .-,-----------------------. |                 
         |       V                         | |                 
         '-WITH----common-table-expression-+-'                
    
    >--+--------------+--------------------------------------------><
       '-WITH--+-RR-+-'   
               +-RS-+     
               +-CS-+     
               '-UR-'

Posting Permissions

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