In SQL Server change column of type int to type text

Question

I would like to change a column in SQL Server from type int, to type text, while maintaining the column name. The table with this column has lots of data, and I do not want to lose it. SQL Server doesn't seem to support implicit or explicit casts from int to text, otherwise this would be pretty simple. So, how would you do it using only SQL?

Answer 1

Just double-cast it through an intermediate type to what you want.

cast(cast(intField as varchar) as text)

Answer 2

Here's a little sample script that shows on possible method:

create table test (id int)

create table test_tmp (id ntext)


insert into test
values (1)
insert into test
values (2)

insert into test_tmp 
select convert(ntext,cast(id as nvarchar)) from test

drop table test

exec sp_rename 'test_tmp','test'

Basically we create a copy of the table, then populate it. We first convert the int to nvarchar then we take to a text value. Finally we drop the old table and rename the temp table.

Answer 3

Since MS SQL Server (like most databases) doesn't support directly changing the type of an existing column, you'll have to do it in steps. The way I have solved problems like this in the past is (assuming your table is named 'foo' and your column is named 'bar'):

1. ALTER TABLE foo ADD COLUMN tempbar text;
2. UPDATE foo SET tempbar = cast(cast(bar as varchar) as text);
3. ALTER TABLE foo DROP COLUMN bar;
4. ALTER TABLE foo ADD COLUMN bar text;
5. UPDATE foo SET bar = tempbar;
6. ALTER TABLE foo DROP COLUMN tempbar;

(Some of the SQL syntax may be off, it's been a year since I last did this, at a different job, so I don't have access to MS SQL Server or the source. You'll also have to do more stuff if your column has an index on it.)

Props to Donnie for the conversion syntax.

[Edit]

Tom H. suggested using the sp_rename stored procedure to rename tempbar as bar (instead of steps 4-6). That is a MS SQL Server-specific solution that may work for many circumstances. The solution I described will work (with syntax revisions) on any SQL database, regardless of version. In my case, I was dealing with primary and foreign keys, and not just a single field, so I had to carefully order all of the operations AND be portable across multiple versions of MS SQL Server -- being explicit worked in my favor.