Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: SQL Server
Expertise: Beginner
Apr 20, 1997



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Adding a Column at a Specific Position

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?

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)
DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date