Re: SQL - Creating a duplicate of an existing column

[CRPence <CRPbottle@xxxxxxxxx>]

  Realize that the /hacky/ process is limited in function.  Perhaps 
more aptly, there will be a [potentially significant] loss of 
function; e.g. ownership, authorities, RI, triggers, journaling, 
dependencies, identity, etc..   If such a method can safely be 
assumed to be sufficient, then consider that "rename" first followed 
by "create table as" will reduce to just one rename, and that the 
given process loses any data for lack of "insert into select from" 
or a "with data" clause on the create.  Of course all activity 
should be under commitment control to ensure implicit rollback given 
any errors are encountered in the script of statements.

Regards, Chuck

Pete Helgren wrote:
> "Look a little further. Then next section in the DB2 SQL
> Reference covers DESCRIBE TABLE."
>
> <<SNIP>>  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