Re: SQL - Creating a duplicate of an existing column

"Look a little further. Then next section in the DB2 SQL Reference covers DESCRIBE TABLE."

Yes it does. I had a copy of the SQL Reference for Cross Platform Development manual open when I did my search and that is why I missed it. Meanwhile I came up with this hacky workaround but now I'll go back and revisit the DESCRIBE TABLE function.

Pseudo Code: (Hacky workaround)

# Clone the existing table adding the aliased column
execute "create table tmp_rename as (select #{table_name}.*,#{table_name}.#{column_name} as #{new_column_name} from #{table_name}) definition only" # Drop the old column
execute "Alter table tmp_rename drop column #{column_name}"
# Rename the current table
execute "RENAME table #{table_name} to tmp_oldtbl"
# Rename the new table back to old
execute "RENAME table tmp_rename to #{table_name}"
# Drop the old table
execute "Drop table tmp_oldtbl"

The copy of the table and dropping of columns and tables and rename of tables is probably very inefficient so I will take a look at the describe function. Of course I need to copy the data from the old to new table in these steps as well

Thanks.

Pete



Simon Coulter wrote:

On 17/12/2009, at 9:39 AM, Pete Helgren wrote:

Looking at the documentation, it looks like DESCRIBE is used for
retrieving information about stored procedures, not tables or columns.
At least all the examples seem to be using stored procedures.

Look a little further. Then next section in the DB2 SQL Reference covers DESCRIBE TABLE.

Regards,
Simon Coulter.
--------------------------------------------------------------------
FlyByNight Software OS/400, i5/OS Technical Specialists

http://www.flybynight.com.au/
Phone: +61 2 6657 8251 Mobile: +61 0411 091 400 /"\
Fax: +61 2 6657 8251 \ /
X
ASCII Ribbon campaign against HTML E-Mail / \
--------------------------------------------------------------------