Change Data Type - SQL Server Forums

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)

Change Data Type

Author

Topic

s15199d
Starting Member

15 Posts

Posted - 2006-03-21 : 10:41:04

I'm trying to change the data type of an existing column in an existing table. All via SQL Server 2000. Or really about 25 columns' datatype.

I've read that I should be able to do:

ALTER TABLE TABLENAME ADD COLUMNNAME DATATYPE

Where columnname is the column for which I'm trying to change the datatype. I get this error when I try this:

"Column names in each table must be unique. Column name 'Column1Name' in table 'Table1Name' is specified more than once."

I don't think cast or convert are what I'm looking for either...I'm looking to change the actual structure of the table. Also, if you're thinking I need to use "MODIFY" instead of "ADD" that's mySQL syntax not SQL Server 2000 (from what I understand).

Any suggestions would be great!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-21 : 10:44:22

It's pretty well laid out in Books Online:

Alter table <tablename> ALTER column <columnName> <datatype>

Be One with the Optimizer
TG

s15199d
Starting Member

15 Posts

Posted - 2006-03-21 : 10:54:01

ALTER TABLE <<TABLENAME>> ALTER COLUMN [<<COLUMNNAME>>] <<DATATYPE>>

Did not work. When I view the create statment for my table the datatype has not been chaned.

s15199d
Starting Member

15 Posts

Posted - 2006-03-21 : 10:58:23

I lied sort of. It works when I run that one line at a time. But, when I try to do this...

ALTER TABLE <TableName> ALTER COLUMN [COLUMN1] tinyint
ALTER TABLE <TableName> ALTER COLUMN [COLUMN2] tinyint
ALTER TABLE <TableName> ALTER COLUMN [COLUMN3] tinyint
ALTER TABLE <TableName> ALTER COLUMN [COLUMN4] bit
ALTER TABLE <TableName> ALTER COLUMN [COLUMN5] bit
ALTER TABLE <TableName> ALTER COLUMN [COLUMN6] bit
ALTER TABLE <TableName> ALTER COLUMN [COLUMNn] bit

I doen't change the datatypes. Do I need to insert 'GO' between my ATER TABLE lines?

s15199d
Starting Member

15 Posts

Posted - 2006-03-21 : 11:00:45

HAHA I tried the 'GO' between each line...that did the trick! Thanks TG!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources