In the SQL Server int change column, type

Question

I would like to change a column in SQL Server from an int type to type text while retaining the column name. There is a lot of data in the table with this column, and I do not want to lose it. SQL Server does not seem to support implicit or explicit casts from int to text, otherwise it would be pretty simple. So, how would you do this using only SQL?

Answer 1

Since MS SQL Server (like most databases) does not support directly changing the type of an existing column, you will have to do this step by step. The way I solved such problems in the past (if your table is called "foo" and your column is called "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 SQL syntaxes may be disabled, it has been a year since I last did this on a different job, so I don’t have access to MS SQL Server or the source. More if your column has an index on it.)

Confirms Donnie for conversion syntax.

[change]

Tom H. suggested using the sp_rename stored procedure to rename tempbar as bar (instead of steps 4-6). This is a solution for MS SQL Server, which can work in many cases. The solution I described will work (with syntax changes) in any SQL database, regardless of version. In my case, I was dealing with primary and foreign keys, and not just with one field, so I had to carefully organize all operations and be portable in several versions of MS SQL Server - it worked explicitly in my favor.

Answer 2

Just drag it through the intermediate type to the one you need.

cast(cast(intField as varchar) as text)

Answer 3

Here is a small example script that shows a 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' 

We basically create a copy of the table and then fill it. First, we convert int to nvarchar, then we take the text value. Finally, we discard the old table and rename the temporary table.