How does one add a column to the middle of a table?
Submitted by admin on Sat, 2005-12-03 00:53
Oracle only allows columns to be added to the end of an existing table. Example:
SQL> CREATE TABLE tab1 ( col1 NUMBER ); Table created. SQL> ALTER TABLE tab1 ADD (col2 DATE); Table altered. SQL> DESC tab1 Name Null? Type ----------------------------------------- -------- ---------------------------- COL1 NUMBER COL2 DATE
Nevertheless, some databases also allow columns to be added to an existing table after a particular column (i.e. in the middle of the table). For example, in MySQL the following syntax is valid:
ALTER TABLE tablename ADD columnname AFTER columnname;
Oracle does not support this syntax. However, it doesn't mean that it cannot be done.
Workarounds:
1. Create a new table and copy the data across.
SQL> RENAME tab1 TO tab1_old; Table renamed. SQL> CREATE TABLE tab1 AS SELECT 0 AS col1, col1 AS col2 FROM tab1_old; Table created.
2. Use the DBMS_REDEFINITION package to change the structure on-line while users are workining.
»
- Login to post comments
How does one add a column to the middle of a table?
Here is another workaround:
create table emptest as select empno, 1 as id, 'x' as emp_name, ename from emp;In the code above, replace 1 as id, 'x' as emp_name with your new columns.
How does one add a column to the middle of a table?
To add a column in middle of the table of varchar data type:
SQL>create table test (ename varchar2(20),salary number);
Table created
SQL>desc test;
Name Null? Type
---------------------- ----------- ---------------
ENAME VARCHAR2(20) SALARY NUMBER
[i]SQL>rename test to test1;
Table renamed
[ii]SQL>create table test2 (id varchar2(20));
Table created
[iii]SQL>create table test as(select test1.ename,test2.id,test1.salary from test1,test2);
Table created
........................................................................................
SQL>desc test;
Name Null? Type
----------------------------------------- -------- --------------
ENAME VARCHAR2(20)
ID VARCHAR2(20)
SALARY NUMBER