sql query for changing position of columns

Question

0.00/5 (No votes)

See more:

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:

SQL

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 0:38am

Aman.Jen

Updated 30-Jun-13 0:46am

Mike Meinz

v3

Add a Solution

Comments

Mike Meinz 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 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 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.

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

gvprabu · Answer 1 · 2013-07-01T03:30:00

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

Maciej Los · Answer 2 · 2013-06-30T01:03:00

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:

SQL

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)[^]

Posted 30-Jun-13 1:03am

Maciej Los

Comments

Aman.Jen 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 1-Jul-13 9:26am

Simple but effective....

Maciej Los 1-Jul-13 11:19am

Thank you, Gopal ;)

Parag Kotekar · Answer 3 · 2013-06-30T00:56:00

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 :)
But , here is a trick to achieve what you are asking for

SQL

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 !

**Zoltán Zörgő** · Answer 4 · 2013-06-30T00:52:00

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.

sql query for changing position of columns

4 solutions

Solution 4

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0