November 11, 2002 at 3:46 am
What is the T-SQL command to insert a column into a existing table but I want to define the exact position for this new column?
mipo
November 11, 2002 at 4:46 am
there is no direct way to do this, what you can do is select into a temp table, drop orig table and while select into orig table back from temp table add that column name in the desired position....
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
November 11, 2002 at 1:03 pm
You can always use alter to add to the end of the table. If there is an absolute reason to add in a specific position then I suggest a few things.
1) Use EM, it will make the transition easiest.
2) Make sure you have enough room for the size of the table times 2. The reason is for the column to be added EM actually creates a new table, adds the data, deletes the old and renames the new to the old name, putting back all constriants an permissions.
3) Use a view instead if this is for an app to know the structure.
4) Make sure you have the latest SP for your sever installed, there have been issues involving linked servers where metadata is incorrect, when checked it is found that numbers of the columns can be off in sequence. Does not usually happen in column adds but if you ever remove SQL may not correct it.
November 11, 2002 at 1:09 pm
Using the design veiw of the table in the EM one can insert a column in between two columns by right clicking and selecting Insert column.
I think during this process SQL server drops and recreates the table with the new column.As Anteras686 has mentioned make sure you have enough disk space.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply