how to add column in specified location in a table

Question

how to add column in specified location in a table

dastagiri16

Hall of Fame

Points: 3544
More actions
January 9, 2010 at 5:30 am

#215472

hi
I have 3 columns .
I want to add one more column to the table after 2 column.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Why does it have to be after the 2nd column?

If you absolutely have to have the table design in that order (for whatever reason), create a new table, copy all the data over, add all the constraints, drop the old table.

There's no support for adding columns in specific places, because there's no meaning to column 'position' in SQL server. If you want the columns returned in a particular order, change the order they are specified in the select clause.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323504 More actions · Answer 2

by default, all columns are added to the end to a table; to add one in the middle, you have to rebuild the whole table.

SQL doesn't care about column order or even the order of the rows in the data.

for aesthetic reasons (meaning it looks right to you), it's probably easiest to use SQL Server Management Studio to do this, because the designer lets you change the column order, and then does all the hard work for you...it rebuilds the table, as well as movinf data nad all related constraints.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Lowell (1/9/2010)
for aesthetic reasons (meaning it looks right to you), it's probably easiest to use SQL Server Management Studio to do this, because the designer lets you change the column order, and then does all the hard work for you...it rebuilds the table, as well as movinf data nad all related constraints.

That said, if you do use management studio and there are thousands or millions of rows in the table, it could take quite some time to run.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

apat SSCertifiable Points: 5929 More actions · Answer 4

If you use SQL Server 2008, then once you query this table and the output is in the grid then you will be able to just drag the column of your interest to whatever position you want.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

GilaMonster (1/9/2010)
Lowell (1/9/2010)
for aesthetic reasons (meaning it looks right to you), it's probably easiest to use SQL Server Management Studio to do this, because the designer lets you change the column order, and then does all the hard work for you...it rebuilds the table, as well as movinf data nad all related constraints.
That said, if you do use management studio and there are thousands or millions of rows in the table, it could take quite some time to run.

And if it has that many rows, it will probably timeout as well.