alter field 2

[refulgent]

Hi All,

I need to increase length of the field in a table. The only way known to me was to drop the entire table and recreate it with bigger fields.

However, now i need to keep my data...is there any way that can help me to increase the size of the field (like 'alter field' command or something?)?

Thank you,

Kate

 

[dbmsguy]

No, there is no way to alter the length of a field once established. You have a couple of options that might work.

1. create the new table the way you want, and do an 'insert'.
2. bcp out your table, then bcp back in with the table redefined.

either way, you need to deal with constraints.

3. some products (like DBArtisan)will 'let' you do it and do a bunch of mucking around behind the scenes. I'm not sure if sybase Central will allow this.

4. if the columns are varchar, you could alter on a new field update the new field to the same as the old, then null out the column so it releases all the space. you would want to reorg after all that.

probably a few other options you could try. Paul

http://www.dbmsguy.com

 

[refulgent]

Hi Paul,

Thanks for the reply! I have been considering options 1 and 2 (1 is preferable because it seems like its not a lot of trouble to do)
However option 4 seems very interesting could you comment on it some more..(what is alter on a new field update?? etc)

Thank you,

Kate

 

[dbmsguy]

actally, I did a little further research....

in my sybase 12 manuals, there actually is a 'modify' in the alter table command

alter table xxxxx modify col_name varchar(??)

so, if you have sybase 12, you might want to try it. i tried it on my ase 11.9.2 and it didn't work, but i don't have a 12 box to try....

at any rate, option 4 is not something i would actually recommend as you end up with a garbage column, but in a crunch it would save you dealing with a lot of constraints if they are there. lots of downsides however. there is also a drop column command in ase 12.

to do it you would

alter table xxx add new_col varchar(10)


update xxx set new_coll=old_col

update xxx set old_col=''

not very pretty, but it would work and of course you would want to fix it later. anyone doing a select * in a pgm would be messed up though... Paul

http://www.dbmsguy.com

 

[Tim1]

Hi Kate / Paul,

For interest, I have tried the modify column command on v.12 and it does work. However, the twice that we have had to use it, the data has ended up a bit junked!

I think that the recommended way is still to BCP your data out, drop and create a new table the BCP in.

This also has the advantage that anywhere that you select * will still work.

Also, bear in mind that if you ALTER TABLE which stored procs refer to, you will need to recompile these, because alter table can do funny things to them.

Tim

 

[dbmsguy]

thanks Tim,

both good points... Paul

http://www.dbmsguy.com

 

[refulgent]

Paul/Tim,

Yeah... it sounds like the best way to solve my problem would be to do bcpin ...

Thanks a lot,

Kate