devxlogo

Adding a Column at a Specific Position

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)

See also  5 Tips for Working With an Onsite Interpreter
devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist