|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Alter Table ....Add ColumnMost everyone knows how to alter a table and add a column...or else we
can look it up in BOL. But....how do I add a column WHEN I want the column to be the xth column in the table. So if I have Create table Experiment (id int,fname varchar(15),lname varchar(15)) Then I want to alter table Experiment Add SSnumber char(9) But I want the SSnumber field to be the second field....not the 4th as it will appear. Thanks-In-Advance You drop the table and re-create it.
Show quote "Bob" <Go1***@Yahoo.Com> wrote in message news:1155910457.450876.327340@b28g2000cwb.googlegroups.com... > > Most everyone knows how to alter a table and add a column...or else we > can look it up in BOL. > > But....how do I add a column WHEN I want the column to be the xth > column in the table. > > So if I have > > Create table Experiment (id int,fname varchar(15),lname varchar(15)) > > Then I want to > > alter table Experiment Add SSnumber char(9) > > But I want the SSnumber field to be the second field....not the 4th as > it will appear. > > Thanks-In-Advance > Aaron, then I will lose all my data.....of course I know I could just
create the new table...populate it with the old data...then drop the original table and re-name the new table...I suppose that's the best solution. Aaron Bertrand [SQL Server MVP] wrote: Show quote > You drop the table and re-create it. > > > > "Bob" <Go1***@Yahoo.Com> wrote in message > news:1155910457.450876.327340@b28g2000cwb.googlegroups.com... > > > > Most everyone knows how to alter a table and add a column...or else we > > can look it up in BOL. > > > > But....how do I add a column WHEN I want the column to be the xth > > column in the table. > > > > So if I have > > > > Create table Experiment (id int,fname varchar(15),lname varchar(15)) > > > > Then I want to > > > > alter table Experiment Add SSnumber char(9) > > > > But I want the SSnumber field to be the second field....not the 4th as > > it will appear. > > > > Thanks-In-Advance > > > Aaron, then I will lose all my data.....of course I know I could just Or, not messing with column order, and just adding the column at the "end" > create the new table...populate it with the old data...then drop the > original table and re-name the new table...I suppose that's the best > solution. of the table. Can you explain why column order is important in this case? I agree, I think you must recreate the table. When I dont know how
something is done I usually do it through the GUI and run profiler to see how management studio (or EM) handles it. In this case it creates a temp table with the new column order and fills it with the data, drops the original table, renames the temp table to the original name and then throws the primary key back on. PT >I agree, I think you must recreate the table. When I dont know how Which of course, on a large table and/or heavy volume system, could be an > something is done I usually do it through the GUI and run profiler to > see how management studio (or EM) handles it. In this case it creates > a temp table with the new column order and fills it with the data, > drops the original table, renames the temp table to the original name > and then throws the primary key back on. absolute catastrophe. Bob wrote:
Show quote > Most everyone knows how to alter a table and add a column...or else we Column order is irrelevant. You're going to retrieve data from the > can look it up in BOL. > > But....how do I add a column WHEN I want the column to be the xth > column in the table. > > So if I have > > Create table Experiment (id int,fname varchar(15),lname varchar(15)) > > Then I want to > > alter table Experiment Add SSnumber char(9) > > But I want the SSnumber field to be the second field....not the 4th as > it will appear. > > Thanks-In-Advance > table using a SELECT statement, simply specify the fields there in the order that you want them retrieved. Columns in table: Name Date SSN Columns in select: SELECT Name, SSN, Date FROM table You could just add a column, which is then at the end of the schema, AND
then create a VIEW with the columns in the order that you desire and use the VIEW if it is too much trouble to specify the column list in the query (which, by the way, is the [BEST PRACTICE]). Don't use 'SELECT *' -it's signifies laziness! -- Show quoteArnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous "Bob" <Go1***@Yahoo.Com> wrote in message news:1155910457.450876.327340@b28g2000cwb.googlegroups.com... > > Most everyone knows how to alter a table and add a column...or else we > can look it up in BOL. > > But....how do I add a column WHEN I want the column to be the xth > column in the table. > > So if I have > > Create table Experiment (id int,fname varchar(15),lname varchar(15)) > > Then I want to > > alter table Experiment Add SSnumber char(9) > > But I want the SSnumber field to be the second field....not the 4th as > it will appear. > > Thanks-In-Advance >  |
|||||||||||||||||||||||
Other interesting topics