Adding new columns in a spec order

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Adding new columns in a spec order

Author

Topic

dev2dev
Starting Member

48 Posts

Posted - 2007-12-31 : 02:15:40

Hi, I want to a alter a table for adding new columns to it
but i am specific about the order of these new columns i.e., i want to insert new column in middle of existing ones

please help me doing this

i found colid in syscolumns which seems like holding column position, will it be a mess if i manipulate it?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 02:39:10

One method to do this is:-

SELECT t1.Col1,t2.Col2,... INTO Table_Tmp
FROM TableA--get data to temp table

DROP TABLE TableA--drop current table

CREATE TABLE TableA--recreate with new col in correct position
(Col1 <datatype>,
Col2 <datatype>,
.......,
ColNew <datatype>,
......
) 

INSERT INTO TableA--copy data from temp table to main with value for new col
SELECT Col1,Col2,....,<newcolvalue>,....
FROM Table_Temp

dev2dev
Starting Member

48 Posts

Posted - 2007-12-31 : 02:49:06

oh yeah, i forgot, thanks!

what about constraints? will it be in new table (if i remember well it should)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-31 : 03:00:57

for that you need to script out current tables code from enterprise manager and add the new column name & type to it in correct position and use it for re-creating the table.

dev2dev
Starting Member

48 Posts

Posted - 2007-12-31 : 03:26:03

assuming my table1 has cola and colb and want to insert col1 in middle of cola and colb

what am thinking to do is...

and the col1 to table1
add tmp_colb to table 1(same datatype/size as colb)
update table set tmp_colb = colb
remove column colb
rename tmp_colb as colb

now i get cola,col1,colb in order

this works, but wanted to know if any system sp/function to moving/swaping the columns

dev2dev
Starting Member

48 Posts

Posted - 2007-12-31 : 04:04:54

quote:
Originally posted by visakh16

for that you need to script out current tables code from enterprise manager and add the new column name & type to it in correct position and use it for re-creating the table.

i think i didnt read ur post well, this (your) method is faster, instead of swapping columns swapping tables is easier if i have many columns to do this, but this in this method we are inserting whole data so need to choose the best method depending on the columns effected

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-31 : 13:50:26

The only way this should be done is via the swapping tables method. Go to the table designer in EM, add a column and place in the correct order, then click the save change script button to view the code that EM would use. This is the same code that you should use.

And the order of columns is meaningless. For performance reasons, just add the column to the bottom of the table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-02 : 00:53:38

And whenever you refer columns in the front end, dont use rs(0),rs(1),etc and instead use rs("colname"),rs("othercol"),etc where rs is the recordset

Madhivanan

Failing to plan is Planning to fail

dev2dev
Starting Member

48 Posts

Posted - 2008-01-03 : 01:53:37

quote:
Originally posted by tkizer

And the order of columns is meaningless. For performance reasons, just add the column to the bottom of the table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

performance wise its meaningless but what about the business need
column order is very much important for our custom ETL (which uses bcp) to work

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-01-03 : 13:21:49

When I say performance, I mean performance of adding the column. If you reorder the columns, then it will take a long time to insert that data and add the indexes if the table is large.

It doesn't sound like your system is designed properly if you require columns to be in a certain order. Bcp doesn't care about the order of the columns.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

X002548
Not Just a Number

15586 Posts

Posted - 2008-01-03 : 14:22:07

quote:
Originally posted by dev2dev

quote:
Originally posted by tkizer

And the order of columns is meaningless. For performance reasons, just add the column to the bottom of the table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

performance wise its meaningless but what about the business need
column order is very much important for our custom ETL (which uses bcp) to work

So are you saying that business is giving a file in a different format and they can't change it....so, you're changing your model?

Insanity

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2008-01-03 : 14:22:56

quote:
Originally posted by tkizer

Bcp doesn't care about the order of the columns.

I'm quite sure that didn't come out right

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources