Monday, November 19, 2012

Oracle DB: alter table add column after

Terrible news, in Oracle DB you cannot add a column in a specific position, it will always be added at the end.

Why would that matter? Because if you use this statement to copy data from one table to the other:

insert into ONE select * from TWO

you rely on ONE and TWO having the same position for each corresponding column.

Here some tips on how to work around (they all entail creating a new table, which sucks):

The safest way is to add at the bottom and in order to copy use:

insert into ONE (COL1, COL2) select COL2, COL1 from TWO; 

and avoid the more generic statement

insert into ONE select * from TWO 

No comments: