Question:
How can I add a column to a table (not just at the end of the table)?
For example:
create table test(first_col char(20),second_col char(20));
I want to alter table “test” so that it looks like this:
first_col char(20),NEW_col char(20),second_col char(20)
Is it possible? If it is, how?
Answer:
This is not possible in standard SQL, nor with any of the databases with which I am familiar. Position of the column, by definition, does not matter to a relational database?it may matter to the human being, but not to the database. If you want to see your table in the “proper” order, you have two choices: create a new table with the columns in the proper order and load the data into the new table; or CREATE a VIEW and access the VIEW rather than the table.
It’s possible to insert a column in a table using Enterprise Manager but behind the scenes (if you look at what happens in Profiler when you insert the column) it actually saves the data to a temporary table, drops the table, re-creates it, and then inserts the data. This is because T-SQL supports only the ability to add a column to a table through the ALTER TABLE command like this:
ALTER TABLE Test ADD NEW_col char(20)
