Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 7,2000 » Administration » Alter column order
Add to briefcase
12 posts, Page 1 of 2
12»»

Alter column order Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
niranjankumar_k
Posted Thursday, February 28, 2008 2:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2008 11:51 PM
Points: 41, Visits: 119
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.
Post #461499
John Rowan
Posted Thursday, February 28, 2008 2:23 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 10:54 AM
Points: 3,837, Visits: 3,821
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.



John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #461500
niranjankumar_k
Posted Thursday, February 28, 2008 2:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2008 11:51 PM
Points: 41, Visits: 119
Without droping this table , anyother way just to mention in right order for new column ?
Post #461501
Anipaul
Posted Thursday, February 28, 2008 2:51 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:03 AM
Points: 4,786, Visits: 1,335
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;


Post #461510
Andras Belokosztolszki
Posted Thursday, February 28, 2008 2:52 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
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
Post #461511
Ian Yates
Posted Thursday, February 28, 2008 5:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Sunday, September 16, 2012 3:26 AM
Points: 1,038, Visits: 443
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... :D

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.


Post #461557
SanjayAttray
Posted Thursday, February 28, 2008 11:29 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, March 15, 2013 2:43 PM
Points: 3,924, Visits: 1,554
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.
Post #461900
Jeff Moden
Posted Thursday, February 28, 2008 11:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:05 AM
Points: 32,894, Visits: 26,775
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... :D

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #461915
chanchal_sheik
Posted Monday, March 25, 2013 10:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 25, 2013 10:15 AM
Points: 1, Visits: 0
SELECT colname1, colname2, colname5, colname4, colname3 into NewTableName from OldTableName

**this will create at NewTable with columns in the above sequence.
Post #1435008
GilaMonster
Posted Monday, March 25, 2013 10:45 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 11:58 AM
Points: 37,665, Visits: 29,918
Please note: 5 year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #1435040
« Prev Topic | Next Topic »

Add to briefcase
12 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.