1

If I have the following table:

|  name  |  value  |
 ------------------
|   A    |    1    |
|   B    |   NULL  |

Where at the moment name is of type varchar(10) and value is of type bit.

I want to change this table so that value is actually a nvarchar(3) however, and I don't want to lose any of the information during the change. So in the end I want to end up with a table that looks like this:

|  name  |  value  |
 ------------------
|   A    |   Yes   |
|   B    |   No    |

What is the best way to convert this column from one type to another, and also convert all of the data in it according to a pre-determined translation?

NOTE: I am aware that if I was converting, say, a varchar(50) to varchar(200), or an int to a bigint, then I can just alter the table. But I require a similar procedure for a bit to a nvarchar, which will not work in this manner.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Seb
  • 959
  • 16
  • 29
  • this may help http://stackoverflow.com/questions/5136013/how-to-change-column-datatype-in-sql-server-database-without-losing-data – Vamsi Prabhala Jul 29 '15 at 15:34
  • 3
    Simplest and safest; Add a new column, update with transform, drop existing column, rename new column – Alex K. Jul 29 '15 at 15:37
  • Do you have any examples on how to use transform to do something like this? @Alex K – Seb Jul 29 '15 at 15:40
  • Just `update T set temp_col=case when value=1 then 'yes' else 'no' end` – Alex K. Jul 29 '15 at 15:42
  • 1
    My question is why do you want to convert a bit to a string representation? Are you going to also create a constraint to the value has to be either 'yes' or 'no'? – Sean Lange Jul 29 '15 at 15:43
  • @Sean Lange - Effectively I want to expand the range of options for the 'value'. Instead of just allowing two different results there I want to allow many different ones, so although after the conversion there will only be 'yes' and 'no', eventually new entries will be made that could be something like 'cat', 'blahblah', etc.... I didn't mention this because I wanted to make the question nice and simple to understand and answer, without unecessary information. – Seb Jul 29 '15 at 15:50
  • @Seb, have you tried Alex's suggestion? – Felix Pamittan Jul 29 '15 at 15:56
  • Yes, it worked perfectly. I thought there may be some obscure command that could handle this sort of thing in one line, but a simpler method is probably safest anyway. Is there some way to mark a comment as the best answer? – Seb Jul 29 '15 at 16:02
  • 1
    I'm afraid there is none. You can post it as an answer to your own question. Be sure to give credit to @AlexK. – Felix Pamittan Jul 29 '15 at 16:04

3 Answers3

3

The best option is to ALTER bit to varchar and then run an update to change 1 to 'Yes' and 0 or NULL to 'No'

This way you don't have to create a new column and then rename it later.

d_luffy_de
  • 967
  • 1
  • 9
  • 24
1

Alex K's comment to my question was the best.

Simplest and safest; Add a new column, update with transform, drop existing column, rename new column

Transforming each item with a simple:

UPDATE Table 
    SET temp_col = CASE
        WHEN value=1 
            THEN 'yes' 
            ELSE 'no' 
        END
Community
  • 1
  • 1
Seb
  • 959
  • 16
  • 29
0

You should be able to change the data type from a bit to an nvarchar(3) without issue. The values will just turn from a bit 1 to a string "1". After that you can run some SQL to update the "1" to "Yes" and "0" to "No".

I don't have SQL Server 2008 locally, but did try on 2012. Create a small table and test before trying and create a backup of your data to be safe.

webpursuits
  • 76
  • 4