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?

possible duplicate of [Add a column to specific position in MSSQL Server](http://stackoverflow.com/questions/7899983/add-a-column-to-specific-position-in-mssql-server) — JNK, Oct 28 '11 at 18:21
Isn't this the column_id field in the sys.columns table (sql server)? — ganders, Feb 06 '15 at 20:31

score 43 · Accepted Answer · answered Oct 28 '11 at 18:21

43

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.

answered Oct 28 '11 at 18:21

Mike M.

12,343
1
24
28

7

You just drag the column in Management Studio. That is pretty easy – paparazzo Oct 29 '11 at 00:13
6

Management Studio can destroy and recreate the table for you, but if you have a very, very huge table with lot's of rows then it's better just to alter it. It would be nice if there was a way to do this without destroying the table. Management Studio actually has an option to stop you from doing this since it can be so time consuming. – Peter Aug 28 '13 at 14:25
2

@PreguntonCojoneroCabrón Management Studio maintains data in the sense that it copies it over to a temporary location if it needs to do a destructive change to a table. Putting a column in a specific order within a table like is one such operation, where the table needs to be dropped and recreated. SSMS copies the data off, drops the table, recreates the table with the new schema, then adds data back to the table. – squillman Jul 21 '16 at 17:38

score 14 · Answer 2 · answered Nov 06 '15 at 18:19

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, <-- 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.

This is the basic gist of how SSDT handles it. 1) Create temp table with new definition. 2) Create Clustered Index on temp table 3) If data exists in old table, `INSERT INTO temp (columns...) SELECT (columns) FROM old` 4) Drop old table 5) sp_rename on table then clustered index 6) recreate all other indexes — Sunny Patel, Oct 29 '20 at 17:32

score 4 · Answer 3 · answered Oct 28 '11 at 18:25

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.

score 2 · Answer 4 · edited Nov 23 '22 at 09:51

2

/* Script to change the column order of a table
Note this will create a new table to replace the original table.
WARNING : Original Table could be dropped.
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';

edited Nov 23 '22 at 09:51

Fedeco

846
10
28

answered Sep 12 '13 at 04:38

Glyn

29
1

While this is a good solution, it will drop the original table. Do not attempt it if you don't know what you're doing – Fedeco Nov 22 '22 at 09:12

score 2 · Answer 5 · answered Feb 06 '15 at 20:15

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.

score 1 · Answer 6 · edited Jan 14 '19 at 17:47

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.

score 1 · Answer 7 · answered Aug 27 '14 at 13:10

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 < 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

Something that could be improved about that procedure is that you can add default value to the new column, then split the old columns in 2 parts: before and after the new column. When you insert the values, you add '@beforeColumn + default + @after column' in the select statement of the insert. Please suggest more improvements if available :) — Zlatin Todorinski, Aug 29 '14 at 08:16

score 1 · Answer 8 · answered Sep 25 '19 at 06:54

1

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

answered Sep 25 '19 at 06:54

Per Johansson

132
1
5

score 1 · Answer 9 · answered Oct 29 '11 at 00:16

1

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.

answered Oct 29 '11 at 00:16

paparazzo

44,497
23
105
176

ping George · Answer 10 · 2021-11-30T03:34:24.227

Create New Add new Column Table Script ex: [DBName].[dbo].[TableName]_NEW
COPY old table data to new table: INSERT INTO newTable ( col1,col2,...) SELECT col1,col2,... FROM oldTable
Check records old and new are the same:
DROP old table
rename newtable to oldtable
rerun your sp add new colum value

-- 1. Create New Add new Column Table Script
CREATE TABLE newTable
(   [new_column] [int] NOT NULL, <-- new column has been inserted here!
    [idx] [bigint] NOT NULL,
    [name] [nvarchar](30) NOT NULL,
    [active] [bit] NOT NULL
)
-- 2. COPY old table data to new table:
INSERT INTO newTable ([new_column],[idx],[name],[active])
SELECT [new_column],[idx],[name],[active]
FROM oldTable
-- 3. Check records old and new are the same: 
select sum(cnt) FROM (
SELECT 'table_1' AS table_name, COUNT(*) cnt FROM newTable 
UNION
SELECT 'table_2' AS table_name, -COUNT(*)  cnt FROM oldTable
) AS cnt_sum 
-- 4. DROP old table
DROP TABLE oldTable
-- 5. rename newtable to oldtable
USE [DB_NAME]
EXEC sp_rename newTable, oldTable

score -3 · Answer 11 · answered Oct 28 '11 at 18:20

-3

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.

answered Oct 28 '11 at 18:20

JNK

63,321
15
122
138

3

well, it might not if I was accessing it via SQL directly, but I'm not. Also I'd like all my tables to be setup the same (upgrades and newly created). – Justin808 Oct 28 '11 at 20:45
11

Column order appears in management studio. On very big tables order can matter for convention's sake. – Peter Aug 28 '13 at 14:21
Although not an answer to the question, it's still the funniest answer I've come across – John Baker Sep 28 '18 at 18:50
The order of the columns may be important, in case you have billions of entries, for instance. Then it may be interesting to make an [alignment padding](https://stackoverflow.com/questions/2966524/calculating-and-saving-space-in-postgresql/7431468#7431468), and it may save you some gigabytes. – lucastamoios Sep 11 '20 at 12:59

How to add a column in TSQL after a specific column?

11 Answers11

solution: