Adding a new column in between existing Columns

[rajeevnandanmishra]

Suppose i have a table with following definition.

CREATE TABLE myTable (col1 Int, col2 Varchar(10))

Now I want to add one more column colX (datetime) in between col1 & col2. So the new table structure should be like

col1 Int, colX Datetime, col2 Varchar(10)

Previously, i was doing this by backing up the table and then deleing the old table, creating a new one and then inserting the records from the backup table.


Can i do the same thing with Alter Table statement or any other method which does not need to take the backup/ droping of existing table.


All helps are highly appreciated.

Rajeev

 

[Strannik]

I guess not. Sure you can manipulate logical column order in the syscolumns table. But you unable to change physical column order(and data storage).

 

[ecobb]

Open your database in Enterprise Manager, click on "Tables", select the table you wish to change, right click on the mouse and select "Design Table". Select the column AFTER the one you want to create, right click on the mouse and select "Insert Column". It will insert a new column ABOVE the one you currently have selected. That's it.

Hope This Helps.

 

[Strannik]

to Ecobb:

That's right. But rajeevnandanmishra needs to add new column between existing ones WITHOUT recreating tables.

Whereas if you press 'Save change script' in EM you will note that table is dropped, new table is created and data are copied to newly created table.

 

[gavinhuet]

This was posted last week and might provide a solution:
thread183-630984

-- Gavin

 

[ecobb]

Wow, I never knew that Enterprise Manager would drop/recreate/copy everything! I guess you learn something new every day!

Thanks!