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

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.

Answer 2

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

cast(cast(intField as varchar) as text)