SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Alter column order


Alter column order

Author
Message
niranjankumar_k
niranjankumar_k
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 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.
John Rowan
John Rowan
SSCertifiable
SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)SSCertifiable (7.8K reputation)

Group: General Forum Members
Points: 7808 Visits: 4564
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
niranjankumar_k
niranjankumar_k
SSC-Enthusiastic
SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)SSC-Enthusiastic (123 reputation)

Group: General Forum Members
Points: 123 Visits: 119
Without droping this table , anyother way just to mention in right order for new column ?
Anipaul
Anipaul
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8143 Visits: 1407
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;



Andras Belokosztolszki
Andras Belokosztolszki
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2951 Visits: 1585
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 Smile. 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
Ian Yates
Ian Yates
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2548 Visits: 445
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 BigGrin

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.



SanjayAttray
SanjayAttray
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4757 Visits: 1619
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118399 Visits: 41480
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 BigGrin

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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
chanchal_sheik
chanchal_sheik
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 0
SELECT colname1, colname2, colname5, colname4, colname3 into NewTableName from OldTableName

**this will create at NewTable with columns in the above sequence.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118428 Visits: 45543
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 recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search