In SQL Server change column of type int to type text

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'):

ALTER TABLE foo ADD COLUMN tempbar text;UPDATE foo SET tempbar = cast(cast(bar as varchar) as text);ALTER TABLE foo DROP COLUMN bar;ALTER TABLE foo ADD COLUMN bar text;UPDATE foo SET bar = tempbar;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.

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

cast(cast(intField as varchar) as text)

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

create table test (id int)create table test_tmp (id ntext)insert into testvalues (1)insert into testvalues (2)insert into test_tmp select convert(ntext,cast(id as nvarchar)) from testdrop table testexec 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.