How to Add Column at Specific Location in Table in SQL

Question

Upendra Pratap Shahi · Answer

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 Menoth · Answer

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

Pankaj Kumar Choudhary · Answer

Hello @shushil AFTER Command doesn't work in sql server for adding a column after a specific column . this query only work for MYSQL

shushil kumar · Answer

ALTER TABLE tablename ADD columnname Datatype AFTER anothercolumn

Rajeesh Menoth · Answer

Hi, Check your syntax,it's wrong..Write the correct one..

shushil kumar · Answer

Incorrect syntax near 'AFTER'.

shushil kumar · Answer

Incorrect syntax near ‘AFTER’.

Upendra Pratap Shahi · Answer

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

Rajeesh Menoth · Answer

Hi, Ref this : http://blog.sqlauthority.com/2013/03/11/sql-server-how-to-add-column-at-specific-location-in-table/

Pankaj Kumar Choudhary · Answer

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]';