Saving Existing Data

Go Up to Before using ALTER TABLE

Before modifying an existing column definition using ALTER TABLE, you must preserve existing data, or it will be lost.

Preserving data in a column and modifying the definition for a column is a five-step process:

1. Add a temporary column to the table whose definition mirrors the current column to be changed (the "old" column).
2. Copy the data from the old column to the temporary column.
3. Modify the old column.
4. Copy the data from the temporary column to the old column.
5. Drop the temporary column.

For example, suppose the EMPLOYEE table contains a column, OFFICE_NO, defined to hold a data type of CHAR (3), and suppose that the size of the column needs to be increased by one.

An example:

The following example describes each step and provides sample code:

First, create a temporary column to hold the data in OFFICE_NO during the modification process:

ALTER TABLE EMPLOYEE ADD TEMP_NO CHAR(3);

Move existing data from OFFICE_NO to TEMP_NO to preserve it:

UPDATE EMPLOYEE
 SET TEMP_NO = OFFICE_NO;

Modify OFFICE_NO, specifying the data type and new size:

ALTER TABLE ALTER OFFICE_NO TYPE CHAR(4);

Move the data from TEMP_NO to OFFICE_NO:

UPDATE EMPLOYEE
 SET OFFICE_NO = TEMP_NO;

Finally, drop the TEMP_NO column:

ALTER TABLE DROP TEMP_NO;

Note: This is the safest, most conservative method for altering a column, following the rule that you should always save existing data before modifying metadata. But for experienced InterBase users, there is a faster, one-step process. You can alter the column without first copying the data, for example: ALTER TABLE EMPLOYEE ALTER COLUMN OFFICE_NO TYPE CHAR(4) which achieves the same end as the five-step process example.