CODEWITHSUNDEEP
sqlsql-server-2000
Alaa
Alaa

Reputation: 575

alter the data type of a column

I need to alter the data type of a column in one table of mine, but this table contains data my problem is that I can't empty the table to do my purpose so I need to Alter the column data type without emptying the table.

What can I do ?

Upvotes: 1

Views: 2691

Answers (2)

Shraddha
Shraddha

Reputation: 2335

have you tried Alter Table 

ALTER TABLE table_name
MODIFY column_name column_type;

The data type of text, ntext and image columns can be changed only in the following ways:

  • text to varchar(max), nvarchar(max), or xml
  • ntext to varchar(max), nvarchar(max), or xml
  • image to varbinary(max)

For other conversions you will have to do casting. Refer http://technet.microsoft.com/en-us/library/ms187928.aspx for casting

Upvotes: 0

fge
fge

Reputation: 121692

Your best choice here is to create a new table new_table with the appropriate columns.

Once created, insert into this table the values of the existing table table as you want them. Only then reintroduce constraints etc (if you do this before insert, you'll penalize insert performance).

Once done, rename table to old_table and new_table to table.

Of course, you need to do this with your application offline.

Upvotes: 2

Related Questions