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

How to Add Column at Specific Location in Table in SQL

How to Add Column at Specific Location in Table in SQL

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (10)