DB2 add New Column in a specific position

[julen]

Hi,

I have a DB2 AS400 database. Here, i have a table, with 3 columns:

column1 columna columnb


I want to insert a new column between column1 and columna.

Is it posible with a db2 sentence??


Thanks.

 

[blom0344]

I think you need to unload the data to a temp table (like make a copy), change the original table DDL and then run an insert statement that reads data from temp to modified table.

Insert into table
(select column1,null,columna,columnb from temptable)

Ties Blom

 

[julen]

Yes, this is the way that i don't want to do. i mean a db2 sentence to insert it.


Thanks

 

[blom0344]

There is an 'add column' operation available on AS400, but AFAIK this will append a column to the existing ones.
Not sure if you can add a new column between existing ones.

Check:

http://publib.boulder.ibm.com/html/as400/v4r5/ic2924/index.htm?info/db2/rbafzmstatabl.htm

Ties Blom

 

[fredericofonseca]

Julen, There is NEVER a way to do it in ANY database.

Several reasons for it not to be possible, and most important of all is that the order of columns on any table is NOT important, as it is your SQL that should determine the retrieval order of the columns.

Only way for you to do it is to either create a temp table, or to use unload/load statements, and change the original table to have the columns on your desired order.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[ddiamond]

Insert into table
(select column1,null,columna,columnb from temptable)

If your table is too large to make a bulk insert pratical, you can always create a view of the temptable that has the extra column, and use the cpyf command to copy the view into your original table.

CREATE VIEW TEMPTABLE_VW ( column1, new_column, columna, columnb ) 
AS select column1, null, columna, columnb from temptable ;

CPYF FROMFILE(TEMPTABLE_VW) TOFILE(TABLE) MBROPT(*ADD)