structure changes

[gentforreal]

Using SQL statements I find it easy to add and drop fields in a table, but does a statement exist that only changes the field width?

As an example, the field width of the zipcode is currently c(5), I want to change it to c(10) so I may populate it with the additional zip+4 digits.

 

[SBTBILL]

Have you tried modify structure?

If remote send the new table over. Append from the old. Rename the old. Rename the new. Delete old when certain all worked well.

Bill Couture
http:\\

http://WWW.SBTBILL.COM

 

[gentforreal]

No, haven't tried that way... I need to dig futher in all the code to see if thats an option, but you gave me an excellent idea how to easily (maybe) do this with the modi command. Guess I was looking for an easy way out.. LOL... Thanks!

 

[ramani]

HI
Have you tried..

ALTER TABLE TableName1 ALTER [COLUMN] FieldName1 FieldType
[(nFieldWidth [, nPrecision])] [NULL | NOT NULL]
[CHECK lExpression1 [ERROR cMessageText1]] [DEFAULT eExpression1]
[PRIMARY KEY | UNIQUE] [REFERENCES TableName2 [TAG TagName1]]
[NOCPTRANS] [NOVALIDATE]

Look into the help on ALTER TABLE command.
ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[gentforreal]

THATS IT!! Thanks.. Its exactly the syntax I was looking for ... I didnt see a reference to the [alter column] in my books. Guess I need new books.. But then...., I have TeK Tips.

Thanks Ramani!!

 

[gentforreal]

A related question..;

In an alter table statement ex: Alter table add column [columnname] ... the column is always added to the bottom of the field list. Is there a way to specify the position within the table the new column is placed? Does my question make sense?

Thanks,
Will

 

[ramani]

Using SQL statements I find it easy to add and drop fields in a table, but does a statement exist that only changes the field width?

ALTER TABLE temp ALTER COLUMN fieldName C(10)

The above will change the column width from whatever it was to 10, and carry all DATA in that limited to 10 character width.

I dont think you can insert inbetween columns.. unless.. you follow some other method..
Example..
USE myTable
PACK
COPY TO TEMP with CDX
CREATE TABLE myTable (new field statemenst....)
APPEND FROM temp
..
Now recreate the INDEX TAGS as it exists in Temp
..


ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[gentforreal]

Thats how I have been trying to do it, but I always work with different tables tables each day. No two databases are ever exactly the same. In each case however, I need to insert a particlar field (zip code) as the 7 field in the table in order to automate my process via a program. There is always a round about way of doing it I suppose. I think scattering and gathering arrays might be the trick I need to learn. Sound about right? Something I have never had to try before. Wish me luck.

Thanks for all your help!!

 

[gentforreal]

Cant seem to do it with arrays either...

Sigh....

 

[Mike Gagnon]

Cant seem to do it with arrays either...

Sigh....

What are trying to do? Adding an element to an array? Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first

 

[furmious]

I have found with SQL that you can do the following to change a character field length.

Select left(zip,10) as zip from mytable into dbf yourtable

 

[wgcs]

As an alternative to new books, I wanted to point out that Ramani's syntax diagram is straight out of the help file that comes with VFP. You may want to look closer at it before spending more money on books....

 

[gentforreal]

Changing the field size became a piece of cake thanks to your help here. Thanks to all for you help.

ok.. here is what I was trying to do....

I get different structures daily.
Basically they are address files

name
user field
address
city
state
user field <----- insert new field above this one.
zip
and so on.....

But not always the exact same. They vary wildly.

At some point however, in the program I run a field needs to be inserted at the 6th position.
field1, field2, field3, field4, field5, field6(insert new field), reposition fields 7- to whatever amount.

Make sense?

Hey.. you are right, the help file is a good one!!