Alter column order
-
Table contain 6 column in order like
A
B
D
E
F
G
Need to addd one more column "C" after B column. (in between B and D ). how to write alter script for this.
-
The ALTER TABLE ADD COLUMN command will add the new column to the end of the table. If you need to change the position of a column within a table, you have to re-create the table. Here's what you'll need to do:
1. Create new table with correct column order.
2. INSERT into new table from old table.
3. Drop FK constraints from old table.
4. Drop old table
5. rename new table to old name
6. Recreate FKs.
-
Without droping this table , anyother way just to mention in right order for new column ?
-
test=# create table TABLE1 (COL1 int, COL3 int);
CREATE TABLE
test=*# insert into TABLE1 values (1,3);
INSERT 0 1
test=*# commit;
COMMIT
test=# begin;
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;
-
niranjankumar_k (2/28/2008)
Without droping this table , anyother way just to mention in right order for new column ?Unfortunately John is right, rebuilding your table is the only alternative. However, I have always been wondering why column order is important in tables? Indexes of course are different :). Columns should be looked up by names, and not by sequence numbers. Even if you use select * (bad practice), lookup by name should work. If you have a very large number of columns then of course it may make sense to group them logically. Could you describe your reasons for forcing a particular column order?
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
I second Andras - I'm wary of software that absolutely requires the columns be in exact order due to their use of select * in queries and then accessing the columns by index 0, 1, 2 rather than by name. It's a bad practice along the lines of tricks to get "order by" in views. It creates instant doubt in the ability of the database designer to have made it efficient (no server-side cursors, etc) and you have to double-check everything they say when you report problems with the app... :w00t: 😀
The easy way to get the columns nicely ordered is to use the GUI tools to do the work for you. Use the tool to insert the column and then either save it and let it do the work or save a script. It will take care of dropping and recreating all indices, foreign key constraints, etc as you could well overlook something or make a mistake.
I guess it's nice from a database diagram perspective to have the columns nicely ordered and, depending on your reporting tools and how end-users use them it's also nice to have the columns in a particular order. A purist would say that the end user, if they must use a reporting tool, should only be accessing views anyway in which case you can change the columns by simply changing the view. If this is your situation then consider using views.
-
niranjankumar_k (2/28/2008)
Without droping this table , anyother way just to mention in right order for new column ?If you don't want to write all script of what John had said. Then open design table window for that table and then create a new column in the place you want to. Then before saving the changes just click the create script option (a page like icon) on the window. you will get the script. Copy it and execute it on QA. But make sure you are not saving changes in design table window.
If the table size is big then I will take backup of table and then execute this script. You have trouble some time, in second but last step it truncates data from the original table and tab_locks sometimes are so long that it fails the insert operation from temp table and you are tend to loose all your data.
SQL DBA.
-
Ian Yates (2/28/2008)
I second Andras - I'm wary of software that absolutely requires the columns be in exact order due to their use of select * in queries and then accessing the columns by index 0, 1, 2 rather than by name. It's a bad practice along the lines of tricks to get "order by" in views. It creates instant doubt in the ability of the database designer to have made it efficient (no server-side cursors, etc) and you have to double-check everything they say when you report problems with the app... :w00t: 😀The easy way to get the columns nicely ordered is to use the GUI tools to do the work for you. Use the tool to insert the column and then either save it and let it do the work or save a script. It will take care of dropping and recreating all indices, foreign key constraints, etc as you could well overlook something or make a mistake.
I guess it's nice from a database diagram perspective to have the columns nicely ordered and, depending on your reporting tools and how end-users use them it's also nice to have the columns in a particular order. A purist would say that the end user, if they must use a reporting tool, should only be accessing views anyway in which case you can change the columns by simply changing the view. If this is your situation then consider using views.
I third that... and if you just gotta have it that way, don't reinvent the wheel... use EM to make the change and copy the script it produces if you need to keep it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
SELECT colname1, colname2, colname5, colname4, colname3 into NewTableName from OldTableName
**this will create at NewTable with columns in the above sequence.
-
Please note: 5 year old thread.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
GilaMonster (3/25/2013)
Please note: 5 year old thread.And they left out the important parts, to boot! 🙂
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
chanchal_sheik (3/25/2013)
SELECT colname1, colname2, colname5, colname4, colname3 into NewTableName from OldTableName**this will create at NewTable with columns in the above sequence.
Correct. But although it will correctly copy the IDENTITY property and NULLability constraints if done on the same instance, it will not create some of the more important parts like the PK, AKs, FKs, Indexes, Triggers, etc, etc.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Yes there is an alternative method, You need to do the above step first, then go to the design view of the column and change column order and save. it will work
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply