Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL
Hi,
I have created table with columns col-A col-B col-C col-D.
I felt need of adding one more column to that table col-X.
So I added that col using SQL statement:
 
Alter Table table_name ADD col-X int null;
 

and it added to last position i.e after col-D,
But I want to add that column col-X to position of col-A i.e to first position.
 
Plz help me with , what query should be fired to change the position of column in SQL.
 

 
Thxnx
Posted 30-Jun-13 1:38am
Edited 30-Jun-13 1:46am
Mike Meinz22.8K
v3
Comments
Mike Meinz at 30-Jun-13 6:45am
   
It is not possible with ALTER statement. If you wish to have the columns in a specific order, you will have to create a newtable, use INSERT INTO newtable (col-x,col-a,col-b) SELECT col-x,col-a,col-b FROM oldtable to transfer the data from the oldtable to the newtable, delete the oldtable and rename the newtable to the oldtable name.
 
This is not necessarily recommended because it does not matter which order the columns are in the database table. When you use a SELECT statement, you can name the columns and have them returned to you in the order that you desire.
Aman.Jen at 30-Jun-13 6:54am
   
Okay...then is it possible to add new column to specific position in table instead of creating new table....
Mike Meinz at 30-Jun-13 7:01am
   
My version of SQL Server Management Studio software (2012) does not allow me to do it. See my first comment. That is how you can do it. But, again, you do not need to do it. Your SELECT statements should contain the columns to be retrieved in the order that you want them returned.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

You need to re-create table_name to new_name with custom ordering. Then copy data from table_name to new_name, drop table_name and change new_name to table_name. That's all!
 
Example:
CREATE TABLE new_name (ColX INT IDENTITY(1,1), ColA INT, ColB VARCHAR(50), ColC VARCHAR(255))
 
INSERT INTO new_name (ColA, ColB, ColC)
SELECT ColA, ColB, ColC
FROM table_name
 
DROP table_name
 
sp_rename new_name, table_name
 
More: sp_rename (T-SQL)[^]
  Permalink  
Comments
Aman.Jen at 30-Jun-13 7:27am
   
Thnx guys..whole day I was playing with this stuff...which is not possible..and it is possible only if I create new table....
 
If I change order of column in design in sql studio ..then it creates connection loss issue ..so again i have to create connection...
 

thnx Mike Meinz all of you guys..:)
gvprabu at 1-Jul-13 9:26am
   
Simple but effective....
Maciej Los at 1-Jul-13 11:19am
   
Thank you, Gopal ;)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

I assure you, it makes no difference in which order you have those fields. You should always make statements, that explicitly refer to fields, and do't rely on any assumed order of them.
If you still want to reorder columns, you have to use SQL Management Studio: http://msdn.microsoft.com/en-us/library/aa337556.aspx[^].
Read this too: http://blog.sqlauthority.com/2008/04/08/sql-server-change-order-of-column-in-database-tables/[^]
As you will notice, nobody is encouraging you in trying to do this in t-sql. It can be don, but should not be done. So forget this approach.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Hey Mike Meinz is right that there is no any QUERY to insert the column into a specific position, it will ALWAYS be added to the End on your column set.
and we should not be limited by the index of the column in SQL Smile | :)
But , here is a trick to achieve what you are asking for
 

 
BEGIN
test=*# create table TABLE1_temp as select COL1, null::int as COL2, COL3 from TABLE1;
SELECT
test=*# drop table TABLE1;
DROP TABLE
test=*# alter table TABLE1_temp rename to TABLE1;
ALTER TABLE
test=*# commit;
COMMIT
test=# select * from TABLE1;
 
Hope it's useful !
  Permalink  
v2
Comments
Sudhakar Shinde at 1-Jul-13 9:45am
   
Do you really need COMMIT since you are not using any DML statement?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Hi,
 
You can do this through... management Studio. Edit Table Design Option then you will create the SQL script for your changes made in table Design window. Check the below link...
 
Auto generate change scripts in SQL Server Management Studio SSMS for tables[^]
 
So Using SSMS also u can change the Table Column Position.
 
Regards,
GVPrabu
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 195
1 ProgramFOX 130
2 Maciej Los 105
3 Sergey Alexandrovich Kryukov 105
4 Afzaal Ahmad Zeeshan 82
0 OriginalGriff 6,564
1 Sergey Alexandrovich Kryukov 6,048
2 DamithSL 5,228
3 Manas Bhardwaj 4,717
4 Maciej Los 4,150


Advertise | Privacy | Mobile
Web03 | 2.8.1411022.1 | Last Updated 1 Jul 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100