How to Add Column at Specific Location in Table in SQL

shushil kumar

11y3.6k

How to Add Column at Specific Location in Table in SQL

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

create table [NewTable]
(
    [old_column_1] int
    ,[new_column_1] varchar(max) -- new col at location
    ,[old_column_2] int
);

insert into [NewTable] ( [old_column_1], [old_column_2] ) 
select [old_column_1], [old_column_2] from [OldTable] (nolock);

drop table [OldTable];

sp_rename '[NewTable]', '[OldTable]';

Rajeesh Menoth11y ago
Hi shushil kumar,

Read the article : http://blog.sqlauthority.com/2013/03/11/sql-server-how-to-add-column-at-specific-location-in-table/

The reason is simple as the above syntax is valid in MySQL but is not valid in SQL Server. In SQL Server following syntax is valid:
ALTER TABLE tablename ADD columnname INT

0
Pankaj Kumar Choudhary11y ago
Hello @shushil AFTER Command doesn't work in sql server for adding a column after a specific column .

this query only work for MYSQL
0
shushil kumar11y ago
ALTER TABLE tablename ADD columnname Datatype AFTER anothercolumn
0
Rajeesh Menoth11y ago
Hi,

Check your syntax,it's wrong..Write the correct one..
0
shushil kumar11y ago
Incorrect syntax near 'AFTER'.
0
shushil kumar11y ago
Incorrect syntax near ‘AFTER’.
0
Upendra Pratap Shahi11y ago
http://blog.sqlauthority.com/2013/03/11/sql-server-how-to-add-column-at-specific-location-in-table/
0
Rajeesh Menoth11y ago
Hi,

Ref this : http://blog.sqlauthority.com/2013/03/11/sql-server-how-to-add-column-at-specific-location-in-table/
0
Pankaj Kumar Choudhary11y ago
hello @Shushil

To my knowledge there is no known method to change the order of the column.But you can achieve this by two ways.
first is:
go into SQL Server management Studio, and "design" an existing table. Insert a column in the middle and look at the script it creates.
nserts all data from old into new, drops the old table and then uses a stored procedure to rename the new table to old.
create table [NewTable] ( [old_column_1] int ,[new_column_1] varchar(max) -- new col at location , [old_column_2] int ); insert into [NewTable] ( [old_column_1], [old_column_2] ) select [old_column_1], [old_column_2] from [OldTable] (nolock); drop table [OldTable]; sp_rename '[NewTable]', '[OldTable]';
0