changing the position of column by writing a query

[mary mathews]

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

[deepuv04]

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

[ck9663]

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:

select id. companyName, EmpName from YourTable