Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2009
    Location
    Connecticut
    Posts
    15

    Unanswered: Moving data between tables

    Db2 LUW V 9.5

    I have a table with 50 columns and 12000 rows of data.

    Modeler has given me new DDL for the table. Columns are in a different order and Two columns have been added.

    in SQL Server you have DTS to do this kind of column re-mapping..

    What is the best way to move this data from the old table into the new table with it's additional columns and different column order?
    Last edited by jrichardson; 04-17-09 at 10:56.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    With only 12,000 rows, the INSERT statement should work fine.

    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by jrichardson
    move this data from the old table into the new table with it's additional columns and different column order?
    Why would you care about the column order? Just add the two new columns to the existing table.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Apr 2009
    Location
    Connecticut
    Posts
    15
    The data modeler wants the columns in a certain order.. and they changed the names of some of the columns..
    in SQL server we have DTS to do this kind of column remapping...
    so would I do a select from the old table in the new order and then an insert into with the new order and new column names in the same statement?

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Yes, if you are stuck creating the new table and being able to modify the old table, then do: "INSERT INTO NewTable SELECT FROM OldTable".

    Andy

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by jrichardson
    The data modeler wants the columns in a certain order..
    Does that data modeler realize that having columns in certain order on paper does not at all guarantee that they will be in the same order on disk or in memory?
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Apr 2009
    Location
    Connecticut
    Posts
    15
    I would have thought something like this

    INSERT INTO KCDWHDUT.T9WF2_TEMP from KCDWHDUT.T9WF2 ....

    but I can't get the syntax correct

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I will repeat:

    "INSERT INTO NewTable SELECT ... FROM OldTable"

    Andy

  9. #9
    Join Date
    Apr 2009
    Location
    Connecticut
    Posts
    15
    This is not working for me.
    Code:
    Insert into KCDWHDUT.T9WF2_Temp Select * from KCDWHDUT.T9WF2 
       ORDER BY 
    AGT_DEMO_ID , 
    CREAT_USER_ID , 
    CREAT_TS , 
    UPDT_USER_ID , 
    UPDT_TS , 
    EFF_DT , 
    END_DT , 
    CRNT_REC_IND , 
    RPT_PRCES_STATS_ID , 
    AG_PREV_UDT_USER_ID , 
    AG_PREV_UPTD_TS , 
    CLI_PREV_UDT_USER_ID , 
    CLI_PREV_UPTD_TS , 
    CLIA_PREV_UDT_USER_ID , 
    CLIA_PREV_UPTD_TS ,  
    CLNM_PREV_UDT_USER_ID , 
    CLNM_PREV_UPTD_TS , 
    AGTC_PREV_UDT_USER_ID , 
    AGTC_PREV_UPTD_TS , 
    ZALS_UPDT_USER_ID , 
    ZALS_UPDT_TS , 
    CO_ID , 
    AGT_ID , 
    AGT_DEMO_EFF_DT , 
    AGT_DEMO_END_DT , 
    AGT_PHON_NUM , 
    AGT_CLI_ID , 
    AGT_TAX_ID , 
    AGT_BTH_DT , 
    AGT_DTH_DT , 
    AGT_INDV_GIV_NM , 
    AGT_INDV_MID_NM , 
    AGT_INDV_SUR_NM , 
    AGT_INDV_TITL_TXT , 
    AGT_INDV_SFX_NM , 
    AGT_ADDR_LN_1_TXT , 
    AGT_ADDR_LN_2_TXT , 
    AGT_ADDR_LN_3_TXT , 
    AGT_ADDR_ADDL_TXT , 
    AGT_ADDR_CNTY_CD , 
    AGT_CITY_NM_TXT , 
    AGT_CRNT_LOC_CD , 
    AGT_CRNT_LOC_TXT , 
    AGT_CTRY_CD , 
    AGT_CTRY_TXT , 
    AGT_PSTL_CD , 
    AGT_SPOUS_GIV_NM , 
    AGT_SPOUS_MID_NM , 
    AGT_SPOUS_SUR_NM , 
    LMT_STAT_EFF_DT , 
    LMT_STAT_END_DT ;
    The error I'm getting is
    Code:
    SQL3100W  Column number "40" (identified as "CREAT_USER_ID") in the output DEL 
    format file is longer than 254 bytes.
    Column CREAT_USER_ID is column 40 in the original table and is Column 2 in the new table.. Am I doing my order by wrong?

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    First of all, get rid of the order by clause. It is useless. Who cares what order the rows get inserted into the table. Second of all it needs to be "Insert into KCDWHDUT.T9WF2_Temp Select <list columns in the order that they appear in the NEW table> from KCDWHDUT.T9WF2"

    Andy

  11. #11
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    and I don't think N_I said it enough, forget the order just add the columns. why go through all the extra work and increase DBD length, etc...
    Dave

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Deleted. Because, I might be misunderstood the meaning of what Andy was wrote.
    Last edited by tonkuma; 04-17-09 at 15:41.

Posting Permissions

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