Add column script

[gt8732b]

Does any of you have a good script for adding the column? I cannot use the alter table add column statement because the column to be added has to be between two existing column for the application code to work. I think if I use alter table add column, it will add the new column to the end, right? Right now I am doing create a temporary table with the new column, transfer all existing data to the temp table, drop the exising table, rename the temp table. I am wondering whether a script can be written in native dynamic sql which can take the table name as a parameter at run time. Thank you for your help! Rong

[Halo]

Use:
Accept tab_name prompt 'Enter table name: ';
Create table table_temp ......
Insert into table_temp as select (col1, col2...) from &tab_name;
-
Something like this will prompt the user to enter the table name from which to select the data and insert into another.

[gt8732b]

This will not work. Because the column list will have to change based on the table name input by the user. Thanks for replying! Rong