How to add a column in TSQL after a specific column

Question

I have a table   MyTable     ID     FieldA     FieldB   I want to alter the table and add a column so it looks like   MyTable     ID     NewField     FieldA     FieldB   In MySQL I would so a   ALTER TABLE MyTable ADD COLUMN NewField int NULL AFTER ID    One line  nice  simple  works great  How do I do this in Microsoft s world

User · Accepted Answer

Unfortunately you can t   If you really want them in that order you ll have to create a new table with the columns in that order and copy data  Or rename columns etc  There is no easy way

User · Answer

This is absolutely possible  Although you shouldn t do it unless you know what you are dealing with   Took me about 2 days to figure it out   Here is a stored procedure where i enter  ---database name  schema name is     for readability  ---table name ---column ---column data type   column added is always null  otherwise you won t be able to insert   ---the position of the new column   Since I m working with tables from SAM toolkit  and some of them have   80 columns    the typical variable won t be able to contain the query  That forces the need of external file  Now be careful where you store that file and who has access on NTFS and network level   Cheers   USE  master  GO         Object   StoredProcedure  SP Set   TrasferDataAtColumnLevel     Script Date  8 27 2014 2 59 30 PM         SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO CREATE PROCEDURE  SP Set   TrasferDataAtColumnLevel         database varchar 100        table varchar 100        column varchar 100        position int       datatype varchar 20        AS BEGIN set nocount on exec     declare   oldC varchar 200    oldCDataType varchar 200    oldCLen int  oldCPos int create table Test   dummy int  declare  columns varchar max         declare  columnVars varchar max         declare  columnsDecl varchar max         declare  printVars varchar max          DECLARE MY CURSOR CURSOR LOCAL STATIC READ ONLY FORWARD ONLY FOR  select column name  data type  character maximum length  ORDINAL POSITION  from      database     INFORMATION SCHEMA COLUMNS where table name          table       OPEN MY CURSOR FETCH NEXT FROM MY CURSOR INTO  oldC   oldCDataType   oldCLen   oldCPos WHILE   FETCH STATUS   0 BEGIN  if  oldCPos        position      begin     exec   alter table Test add       column           datatype     null    end  if  oldCDataType      timestamp    begin      set  columns     oldC                set  columnVars             oldC                if  oldCLen is null      begin         if  oldCDataType      uniqueidentifier            begin             set  printVars       print convert       oldCDataType             oldC                      set  columnsDecl             oldC            oldCDataType                       exec   alter table Test add        oldC             oldCDataType      null            end         else         begin             set  printVars       print convert varchar 50         oldC                      set  columnsDecl             oldC            oldCDataType                       exec   alter table Test add        oldC             oldCDataType      null            end     end     else     begin          if  oldCLen  lt  0          begin             set  oldCLen   4000         end         set  printVars       print        oldC          set  columnsDecl             oldC            oldCDataType           convert character  oldCLen                      exec   alter table Test add        oldC             oldCDataType            oldCLen       null        end end  if exists  select column name from INFORMATION SCHEMA COLUMNS where table name     Test   and column name     dummy    begin     alter table Test drop column dummy end  FETCH NEXT FROM MY CURSOR INTO   oldC   oldCDataType   oldCLen   oldCPos END CLOSE MY CURSOR DEALLOCATE MY CURSOR  set  columns   reverse substring reverse  columns   charindex       reverse  columns    1  len  columns    set  columnVars   reverse substring reverse  columnVars   charindex       reverse  columnVars    1  len  columnVars    set  columnsDecl   reverse substring reverse  columnsDecl   charindex       reverse  columnsDecl    1  len  columnsDecl    set  columns   replace replace REPLACE  columns                      char 9    char 9          char 9         set  columnVars   replace replace REPLACE  columnVars                      char 9    char 9          char 9         set  columnsDecl   replace replace REPLACE  columnsDecl                 char 9    char 9         char 9         set  printVars   REVERSE substring reverse  printVars   charindex       reverse  printVars   1  len  printVars      create table query  id int identity 1 1   string varchar max    insert into query values     declare       columnsDecl      DECLARE MY CURSOR CURSOR LOCAL STATIC READ ONLY FORWARD ONLY FOR      insert into query values      select       columns      from      database            table         insert into query values     OPEN MY CURSOR FETCH NEXT FROM MY CURSOR INTO       columnVars      WHILE   FETCH STATUS   0 BEGIN      insert into query values     printVars    insert into query values        insert into Test      insert into query values     columns   insert into query values         values         columnVars           insert into query values     FETCH NEXT FROM MY CURSOR INTO        columnVars      END CLOSE MY CURSOR DEALLOCATE MY CURSOR     declare  path varchar 100      C  query sql   declare  query varchar 500      bcp  select string from query order by id  queryout       path      -t  -c -S         servername       -T    exec master  xp cmdshell  query  set  query      sqlcmd -S        servername      -i       path  EXEC xp cmdshell   query  set  query     del        path  exec xp cmdshell  query  drop table      database            table      select   into      database            table     from Test   drop table query drop table Test       END

User · Answer

Even if the question is old  a more accurate answer about Management Studio would be required   You can create the column manually or with Management Studio  But Management Studio will require to recreate the table and will result in a time out if you have too much data in it already  avoid unless the table is light   To change the order of the columns you simply need to move them around in Management Studio  This should not require  Exceptions most likely exists  that Management Studio to recreate the table since it most likely change the ordination of the columns in the table definitions   I ve done it this way on numerous occasion with tables that I could not add columns with the GUI because of the data in them  Then moved the columns around with the GUI of Management Studio and simply saved them   You will go from an assured time out to a few seconds of waiting

User · Answer

Script to change the column order of a table   Note this will create a new table to replace the original table   HOWEVER it doesn t copy the triggers or other table properties - just the data        Generate a new table with the columns in the order that you require  Select Column2  Column1  Column3 Into NewTable from OldTable  Delete the original table  Drop Table OldTable   Rename the new table  EXEC sp rename  NewTable    OldTable

User · Answer

solution   This will work for tables where there are no dependencies on the changing table which would trigger cascading events  First make sure you can drop the table you want to restructure without any disastrous repercussions  Take a note of all the dependencies and column constraints associated with your table  i e  triggers  indexes  etc    You may need to put them back in when you are done   STEP 1  create the temp table to hold all the records from the table you want to restructure  Do not forget to include the new column   CREATE TABLE  tmp myTable      new column   int  NOT NULL   lt -- new column has been inserted here       idx   bigint  NOT NULL       name   nvarchar  30  NOT NULL       active   bit  NOT NULL     STEP 2  Make sure all records have been copied over and that the column structure looks the way you want   SELECT TOP 10   FROM  tmp myTable ORDER BY 1 DESC -- you can do COUNT    or anything to make sure you copied all the records  STEP 3  DROP the original table   DROP TABLE myTable  If you are paranoid about bad things could happen  just rename the original table  instead of dropping it   This way it can be always returned back   EXEC sp rename myTable  myTable Copy   STEP 4  Recreate the table myTable the way you want  should match match the  tmp myTable table structure   CREATE TABLE myTable      new column   int  NOT NULL       idx   bigint  NOT NULL       name   nvarchar  30  NOT NULL       active   bit  NOT NULL     -- do not forget any constraints you may need  STEP 5  Copy the all the records from the temp  tmp myTable table into the new  improved  table myTable   INSERT INTO myTable   new column   idx   name   active   SELECT  new column   idx   name   active  FROM  tmp myTable   STEP 6  Check if all the data is back in your new  improved table myTable  If yes  clean up after yourself and DROP the temp table  tmp myTable and the myTable Copy table if you chose to rename it instead of dropping it

User · Answer

In SQL Enterprise Management Studio  open up your table  add the column where you want it  and then -- instead of saving the change -- generate the change script   You can see how it s done in SQL   In short  what others have said is right   SQL Management studio pulls all your data into a temp table  drops the table  recreates it with columns in the right order  and puts the temp table data back in there   There is no simple syntax for adding a column in a specific position

User · Answer

If you are using the GUI to do this you must deselect the following option allowing the table to be dropped

User · Answer

In Microsoft SQL Server Management Studio  the admin tool for MSSQL  just go into  design  on a table and drag the column to the new position   Not command line but you can do it

User · Answer

You should be able to do this if you create the column using the GUI in Management Studio   I believe Management studio is actually completely recreating the table  which is why this appears to happen   As others have mentioned  the order of columns in a table doesn t matter  and if it does there is something wrong with your code

User · Answer

You have to rebuild the table   Luckily  the order of the columns doesn t matter at all   Watch as I magically reorder your columns   SELECT ID  Newfield  FieldA  FieldB FROM MyTable   Also this has been asked about a bazillion times before

[tsql] How to add a column in TSQL after a specific column?

Examples related to tsql

Examples related to alter-table