Moving data between tables

jrichardson · #1 (**permalink**) 04-17-09, 09:28

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?

ARWinner · #2 (**permalink**) 04-17-09, 09:32

With only 12,000 rows, the INSERT statement should work fine.

Andy

n_i · #3 (**permalink**) 04-17-09, 09:37

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.

jrichardson · #4 (**permalink**) 04-17-09, 09:54

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?

ARWinner · #5 (**permalink**) 04-17-09, 09:57

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

n_i · #6 (**permalink**) 04-17-09, 10:32

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?

jrichardson · #7 (**permalink**) 04-17-09, 11:11

I would have thought something like this

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

but I can't get the syntax correct

ARWinner · #8 (**permalink**) 04-17-09, 11:20

I will repeat:

"INSERT INTO NewTable SELECT ... FROM OldTable"

Andy

jrichardson · #9 (**permalink**) 04-17-09, 11:46

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?

ARWinner · #10 (**permalink**) 04-17-09, 12:15

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