Alter table question

Question

Alter table question

willem wout

Say Hey Kid

Points: 673
More actions
September 6, 2002 at 6:58 am

#79300

Hi *,
I need to add an extra field to a table, but
this field must be the thirth field of the table, not the last field. I know this question sux in terms of relational theory, but yet...Can it be done without export data / drop table / recreate table / import.?
tnx

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

The short answer is no. If you do this through Enterprise Manager, it creates a new table, migrates the data, drops the old table, and renames the new table to the old table name behind the scenes.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

willem wout Say Hey Kid Points: 673 More actions · Answer 2

🙁 and i needed a 'yes' answer in conjuction with a sql-script format... no enterprise manager. But thanks for the fast answer Kelly!

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 3

Have you considered using SELECT INTO to create another table with the new column in the correct place in the SELECT statement. You can then drop the original table and rename the new table.

The only problem with this approach is that you will need to create any indexes/constraints on the new table, and also doesn't work if there are foreign key constraints to the old table.

NPeeters SSChampion Points: 12538 More actions · Answer 4

How about adding the column (at the end), renaming the table and constructing a view with the correct name AND the correct column order?

willem wout Say Hey Kid Points: 673 More actions · Answer 5

>How about adding the column (at the end), renaming the table and constructing a view with the correct name AND the correct column order?

um, the reason for having the sequence right is because of some tabledata import tool that can't understand that the sequence changed. Messing around with updatable views is not the kind of KISS methode i had in mind, but thanks for the hint.