Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

Adding a Column at a Specific Position

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

 

 

 

 

 


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

 

 

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