Can I change a field/column width in a script ?

Can I change a field/column width in a script ? For example, I have a table
where field COL1 is a varchar(200). The table already has data in it. I
would like to increase the width of field COL1 to be varchar(300). Can I do
this in a script ? Thank you.Assuming no keys or constraints reference the column:
ALTER TABLE tablename
ALTER COLUMN Col1 VARCHAR(300)
You won't lose any data if you do this (but if you went the other way, you
could).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
> Can I change a field/column width in a script ? For example, I have a
table
> where field COL1 is a varchar(200). The table already has data in it. I
> would like to increase the width of field COL1 to be varchar(300). Can I
do
> this in a script ? Thank you.
>|||Sure. You can use alter table...alter column e.g.
alter table YourTable
alter column COL1 varchar(300)
-Sue
On Tue, 27 Jul 2004 16:17:50 -0500, "Fie Fie Niles"
<fniles@.wincitesystems.com> wrote:
>Can I change a field/column width in a script ? For example, I have a table
>where field COL1 is a varchar(200). The table already has data in it. I
>would like to increase the width of field COL1 to be varchar(300). Can I do
>this in a script ? Thank you.
>|||Thank you.
What did you mean by "but if you went the other way, you could lose data" ?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewU7fACdEHA.3596@.tk2msftngp13.phx.gbl...
> Assuming no keys or constraints reference the column:
> ALTER TABLE tablename
> ALTER COLUMN Col1 VARCHAR(300)
> You won't lose any data if you do this (but if you went the other way, you
> could).
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
> > Can I change a field/column width in a script ? For example, I have a
> table
> > where field COL1 is a varchar(200). The table already has data in it. I
> > would like to increase the width of field COL1 to be varchar(300). Can I
> do
> > this in a script ? Thank you.
> >
> >
>|||If you alter the column to a smaller size, you could lose
data.
-Sue
On Tue, 27 Jul 2004 17:05:22 -0500, "Fie Fie Niles"
<fniles@.wincitesystems.com> wrote:
>Thank you.
>What did you mean by "but if you went the other way, you could lose data" ?
>
>"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
>news:ewU7fACdEHA.3596@.tk2msftngp13.phx.gbl...
>> Assuming no keys or constraints reference the column:
>> ALTER TABLE tablename
>> ALTER COLUMN Col1 VARCHAR(300)
>> You won't lose any data if you do this (but if you went the other way, you
>> could).
>> --
>> http://www.aspfaq.com/
>> (Reverse address to reply.)
>>
>>
>> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
>> news:##m9c8BdEHA.1644@.tk2msftngp13.phx.gbl...
>> > Can I change a field/column width in a script ? For example, I have a
>> table
>> > where field COL1 is a varchar(200). The table already has data in it. I
>> > would like to increase the width of field COL1 to be varchar(300). Can I
>> do
>> > this in a script ? Thank you.
>> >
>> >
>>
>|||Well, if you have a varchar(300), and you change it to varchar(200), you
will lose some data in any column that had more than 200 characters...
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
> Thank you.
> What did you mean by "but if you went the other way, you could lose data"
> ?|||Hi ,
I feel that Alter Table command will "FAIL" if we have a column with
varchar(300) and if few columns contains more than 200 characters
already in place, and if you change it to varchar(200).
In this case to alter the column to Varchar(200) we may need to update the
column to have less than = 200 characters
update table
set column = substring(column,1,200)
Remember that the above command will truncate the records which are holding
more than 200 charecters
After that you can alter the table to varchar(200)
alter table xx_tab alter column columnname varchar(200)
Thanks
Hari
MCDBA
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OyklB9DdEHA.3588@.TK2MSFTNGP11.phx.gbl...
> Well, if you have a varchar(300), and you change it to varchar(200), you
> will lose some data in any column that had more than 200 characters...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
> > Thank you.
> > What did you mean by "but if you went the other way, you could lose
data"
> > ?
>|||As Sue says Hari, it does work, it simply truncates the data longer than the
new column width
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:deidg0lj8hba2t076cm1upe5l2fg4l4ukk@.4ax.com...
> Sure. You can use alter table...alter column e.g.
> alter table YourTable
> alter column COL1 varchar(300)
> -Sue
> On Tue, 27 Jul 2004 16:17:50 -0500, "Fie Fie Niles"
> <fniles@.wincitesystems.com> wrote:
> >Can I change a field/column width in a script ? For example, I have a
table
> >where field COL1 is a varchar(200). The table already has data in it. I
> >would like to increase the width of field COL1 to be varchar(300). Can I
do
> >this in a script ? Thank you.
> >
>|||> In this case to alter the column to Varchar(200) we may need to update the
> column to have less than = 200 characters
Right, which means you lose data (or have to put it somewhere else).
A|||Thank you very much, all.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OyklB9DdEHA.3588@.TK2MSFTNGP11.phx.gbl...
> Well, if you have a varchar(300), and you change it to varchar(200), you
> will lose some data in any column that had more than 200 characters...
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Fie Fie Niles" <fniles@.wincitesystems.com> wrote in message
> news:ekHjBXCdEHA.3020@.TK2MSFTNGP11.phx.gbl...
> > Thank you.
> > What did you mean by "but if you went the other way, you could lose
data"
> > ?
>