Sandy wrote:

> Does anyone know of a more efficient way to perform the following
> alteration...
>
> I have a table which is already full of data, and I want to change the
> datatype
> of one of its columns (this column that needs changing might be the primary
> key).
> I have tried using ALTER TABLE commands but they don't seem to allow one
> to make a change like this, so I resorted to a very tedious and slow
> approach of
> making a temporary table (same structure as the one in question, but with
> the datatype
> change), inserting all records into it, clearing out and dropping the
> original table, creating
> a new table (with the correct name, types etc), transferring the data back
> into this one, and
> finally dropping the temporary table.

Well THAT is my preferred way EXCEPT I don't do it in the
existing database. Create a new blank database with your new
structure, pump the data over from the old database, and
everything is safe - and you only copy once!
( Backup - Restore to new database - delete 'problem' table
- create new table - pump table from old database )

> Two problems with this approach are that it can be VERY slow on large
> datasets (half a
> million records) and it also seems to crash sometimes - for example, when I
> want to drop
> one of the tables, it says that the table doesn't exist. A restart of
> IBOConsole allows it
> to continue.

The other way of changing a column if you MUST risk messing
up the original :)
just create a new temporary column in the table, copy the
data to that column. Drop and recreate the old column with
the new format, copy the data back, and then delete the
temporary column.

I believe that is what many of the tools that manage this
sort of change do.

--
Lester Caine
-----------------------------
L.S.Caine Electronic Services