By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

changing the position of column by writing a query

P: 8
is there any way to change the position of a column in a table by writing sql query?

for eg;
Table 'demo' contains 3 fields:
companyName
EmpName
id

I need to move the 'id' from last position to first by writing a query along with the data..

can anybody help me pls...
May 26 '08 #1
Share this Question
Share on Google+
2 Replies


deepuv04
Expert 100+
P: 227
is there any way to change the position of a column in a table by writing sql query?

for eg;
Table 'demo' contains 3 fields:
companyName
EmpName
id

I need to move the 'id' from last position to first by writing a query along with the data..

can anybody help me pls...
Hi,
You can do in two ways.
1. ALTER TABLE TABLE_NAME ADD COLUMN_NAME DATATYPE
then update the table as
update Table_Name set New_Column = Old_Column
then delete the old column
Alter table Table_Name drop column Old_Column
( Create new columns so that they must be in the order you want)

2. Create a new table as
SELECT Id,CompanyName,EmpName into New_Table From Table_Name
then drop the old table
then rename the original table


why do you want to? As per relational database theory the order of the
columns in a table is not important. If you want to see the columns in certain order specify the column names in some specific order in select query

thanks
May 26 '08 #2

ck9663
Expert 2.5K+
P: 2,878
Hi,
You can do in two ways.
1. ALTER TABLE TABLE_NAME ADD COLUMN_NAME DATATYPE
then update the table as
update Table_Name set New_Column = Old_Column
then delete the old column
Alter table Table_Name drop column Old_Column
( Create new columns so that they must be in the order you want)

2. Create a new table as
SELECT Id,CompanyName,EmpName into New_Table From Table_Name
then drop the old table
then rename the original table


why do you want to? As per relational database theory the order of the
columns in a table is not important. If you want to see the columns in certain order specify the column names in some specific order in select query

thanks

I would agree with you.

Mary, If you need this columns re-arrange so that you can BULK INSERT into it, I would suggest you just create a temp table that you can use for that and check the quality of the records that you will be inserting to your table. This way your don't just insert data without knowing it's quality.

If this is for display purposes, just do something l ike:

Expand|Select|Wrap|Line Numbers
  1. select id. companyName, EmpName from YourTable
May 26 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.