SQL Query to add a new column after an existing column in SQL Server 2005

Question

I need a SQL query which add a new column after an existing column  so the column will be added in a specific order   Please suggest me if any ALTER query which do that

User · Answer

If you want to alter order for columns in Sql server, There is no direct way to do this in SQL Server currently.

Have a look at http://blog.sqlauthority.com/2008/04/08/sql-server-change-order-of-column-in-database-tables/

You can change order while edit design for table.

User · Answer

Microsoft SQL  AFAIK  does not allow you to alter the table and add a column after a specific column  Your best bet is using Sql Server Management Studio  or play around with either dropping and re-adding the table  or creating a new table and moving the data over manually  neither are very graceful   MySQL does however   ALTER TABLE mytable ADD COLUMN  new column  lt type gt  AFTER       existing column

User · Answer

ALTER won t do it because column order does not matter for storage or querying  If SQL Server  you d have to use the SSMS Table Designer to arrange your columns  which can then generate a script which drops and recreates the table  Edit Jun 2013  Cross link to my answer here  Performance   Space implications when ordering SQL Server columns

User · Answer

It is a bad idea to select   from anything  period   This is why SSMS adds every field name  even if there are hundreds  instead of select     It is extremely inefficient regardless of how large the table is  If you don t know what the fields are  its still more efficient to pull them out of the INFORMATION SCHEMA database than it is to select     A better query would be   SELECT   COLUMN NAME    Case    When DATA TYPE In   varchar    char    nchar    nvarchar    binary      Then convert varchar MAX   CHARACTER MAXIMUM LENGTH      When DATA TYPE In   numeric    int    smallint    bigint    tinyint      Then convert varchar MAX   NUMERIC PRECISION     When DATA TYPE    bit     Then convert varchar MAX   1    When DATA TYPE IN   decimal    float      Then convert varchar MAX   Concat Concat NUMERIC PRECISION         NUMERIC SCALE      When DATA TYPE IN   date    datetime    smalldatetime    time    timestamp      Then      End As DATALEN    DATA TYPE  FROM INFORMATION SCHEMA COLUMNS  Where   TABLE NAME

User · Answer

According to my research there is no way to do this exactly the way you want  You can manually re-create the table and copy the data  or SSMS can  and will  do this for you  when you drag and drop a column to a different order  it does this   In fact it souldn t matter what order the columns are    As an alternative solution you can select the data you want in the order you desired  For example  instead of using asterisk     in select  specify the column names in some order    Lets say MyTable has col1  col2  col3  colNew columns  Instead of  SELECT   FROM MyTable  You can use  SELECT col1  colNew  col2  col3 FROM MyTable

User · Answer

It s possible    First  just add each column the usual way  as the last column    Secondly  in SQL Server Management Studio Get into Tools    Options   Under  Designers  Tab     Table and Database Designers  menu  uncheck the option  Prevent saving changes that require table re-creation    Afterwards  right click on your table and choose  Design   In  Design  mode just drag the columns to order them   Don t forget to save

User · Answer

First add the new column to the old table through SSMStudio  Go to the database    table    columns  Right click on columns and choose new column  Follow the wizard   Then create the new table with the columns ordered as desired as follows   select   into my new table from   select old col1  my new col  old col2  old col3 from my old table    as A     Then rename the tables as desired through SSMStudio  Go to the database    table    choose rename

[sql] SQL Query to add a new column after an existing column in SQL Server 2005

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005