Example: Insert a Column
When you add a column to an existing table (using the Alter Table dialog in VDBA), the column is placed after the last previously existing column in the table. To insert a new column between existing columns, you must follow a procedure similar to that for renaming a column.
The following example illustrates inserting a new column, newcol, in the middle of an existing table with previously defined columns.
Using SQL:
1. Create a temporary table containing the existing columns from the original table and the new columns. If the column named “newcol” is to be located in the middle of the table structure, you must list all the columns individually. For example:
create table temp as
select col1, col2, varchar(’ ’)
as newcol, col3, col4 from test;
2. Drop the original table.
drop test;
3. Rename the temporary table with the name of the original table using a CREATE TABLE AS statement with a subselect statement.
create table test as
select * from temp;
4. Drop the temporary table.
drop temp;
In VDBA, follow these steps to insert a new column, newcol, into table test:
1. Create a temporary table named temp.
2. Enable Create Table As Select in the Create Table dialog.
3. In the Select Statement edit control, enter the following SELECT statement, inserting the new column:
select col1, col2, varchar(' ') as newcol,
col3, col4 from test
4. Drop the original table, test.
5. Create a new table named test.
6. Enable Create Table As Select in the Create Table dialog.
7. In the Select Statement edit control, enter:
select * from temp
8. Drop the temporary table, temp.
Be sure to recreate integrities, views, indexes, grants, and other dependent objects that were destroyed when the original table was dropped in Step 4.
Note: To rearrange the current column order of a table without adding new columns, use this same procedure, selecting the columns in the desired order in Step 3.