changing data types

Question

Post reply

changing data types

sqlpro

Hall of Fame

Points: 3859
More actions
August 24, 2005 at 6:38 pm

#165359

Hi Friends
I want to change datatype of a column from text to varchar.I know i can do one at a time.but there r too many to do.
is there any quicker way changing datatypes in a specified table.
Thanks

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Ninja's_RGR'us

SSC Guru

Points: 294069

August 24, 2005 at 9:55 pm

#584851

Can you show us exactly what you are trying to do?

Sergei Zarembo Hall of Fame Points: 3887 More actions · Answer 2

probably needs to change datatype of a bunch of columns in a table in one step.

sqlpro Hall of Fame Points: 3859 More actions · Answer 3

Remi

say i have table (mytable) with following cols

pkey int pkey

name varchar(10)

notes text

now i want to change datatype of 3rd col (ie. notes) to varchar say varchar(500).

Hope thats clear.Thanks

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 4

Check BOL under modfying Default definitions. The short answer is that you can not change a column that has a data type of text.

HTH

Mike

sqlpro Hall of Fame Points: 3859 More actions · Answer 5

Hi Michael

I just checked "modfying Default definitions" section but it talks abt "DEFAULTS" not datatypes.

(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_4gvn.asp)

am missing something. i want to change datatype of a columns

Michael Du Bois SSCertifiable Points: 5163 More actions · Answer 6

OOPS , sorry. This may help.

Mike

From BOL

ALTER COLUMN

Specifies that the given column is to be changed or altered. ALTER COLUMN is not allowed if the compatibility level is 65 or earlier. For more information, see sp_dbcmptlevel.

The altered column cannot be:

A column with a text, image, ntext, or timestamp data type.
The ROWGUIDCOL for the table.
A computed column or used in a computed column.
A replicated column.
Used in an index, unless the column is a varchar, nvarchar, or varbinary data type, the data type is not changed, and the new size is equal to or larger than the old size.
Used in statistics generated by the CREATE STATISTICS statement. First remove the statistics using the DROP STATISTICS statement. Statistics automatically generated by the query optimizer are automatically dropped by ALTER COLUMN.
Used in a PRIMARY KEY or [FOREIGN KEY] REFERENCES constraint.
Used in a CHECK or UNIQUE constraint, except that altering the length of a variable-length column used in a CHECK or UNIQUE constraint is allowed.
Associated with a default, except that changing the length, precision, or scale of a column is allowed if the data type is not changed.

Some data type changes may result in a change in the data. For example, changing an nchar or nvarchar column to char or varchar can result in the conversion of extended characters. For more information, see CAST and CONVERT. Reducing the precision and scale of a column may result in data truncation.

sqlpro Hall of Fame Points: 3859 More actions · Answer 7

Thanks for that Michael.

but I just changed a column's datatype to varchar from text from enterprise manager.which worked fine !!

i got following warning ofcourse

- Warning: Data might be lost converting column 'creator' from 'text'.

sqlpro Hall of Fame Points: 3859 More actions · Answer 8

oops!!

i tried to change datatype thru script then i got following error Michael

Cannot alter column 'creator' because it is 'text'.

sqlpro Hall of Fame Points: 3859 More actions · Answer 9

sqlpro

Hall of Fame

Points: 3859

August 25, 2005 at 6:03 pm

#585238

but i can change in EM though

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

You can also change it via script, but you need a workaround for it

IF OBJECT_ID('showme') > 0

DROP TABLE showme

CREATE TABLE showme (c1 TEXT)

GO

INSERT INTO showme SELECT 'Hello World'

ALTER TABLE showme ADD c2 VARCHAR(500)

GO

UPDATE showme SET c2 = SUBSTRING(c1,1,500)

GO

ALTER TABLE showme DROP COLUMN c1

EXEC sp_rename 'showme.c2', 'c1', 'COLUMN'

SELECT * FROM showme

EXEC sp_columns 'showme'

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Smruti-255597 Old Hand Points: 301 More actions · Answer 11

Yes it can be change via EM

Yes it gives the following error

"Data may be lost converting column 'usernsme' from 'varchar(50)' " to text

But no data is lost if there are atleast 5000 records

For more records i h.v not tried

So don't worry

it can be possible

Smruti

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Ninja's_RGR'us

SSC Guru

Points: 294069

August 26, 2005 at 6:43 am

#585349

Nice to see you back from whatever you were doing .

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 13

If you drop from text to varchar 500, any record that contains 501+ characters will be truncated to 500. That's where you would have a loss of data.

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 14

The same fact still applies, wheter it be a script or using EM..

You cannot change (ie alter) a column that is type text to varchar. Frank's script does not change the column, nor does EM.

The only thing you can do (and that is what Frank's script and EM does) is to create a new column as varchar, copy data from the textcolumn over to the varchar column (which will fit as many chars as the varchar is long) and then delete the text column...

The difference may be subtile, but there's quite a big leap from 'change/alter' to 'createNew-copy-deleteOld', at least in the context of SQL Server.

/Kenneth