Can I change data type in sql db column from numeric to nvarchar without loosing data?

Question

Here's the situation, I started working on a CMS developed by someone else, in ColdFusion with mssql database. I an not a sql gouru:-)
When image names are stored in db my collegue choose to rename the image before to a number.jpg I find that completely illogical and would like to be able to use image names in database. Can I change the data type of this field to nvarchar without loosing current data??

Thanks in advance.
Bianca

Answer 1

Probably the safest way is a multistep process:



1) Create a temp table with two (or more) columns - whatever column(s) same as those uniquely identifying your source table rows plus newcol of type nvarchar to hold your converted number

2) INSERT INTO tempTable FROM

(SELECT , CONVERT(nvarchar(length), oldcol) FROM origTable)

3) Inspect a few rows of the temp table to make sure it convereted the number as you expect

4) Use ALTER TABLE to change the datatype of your original table

5) Inspect a few rows to see if the data was converted automatically during the ALTER TABLE. If not, do

UPDATE origTable o SET oldcol =

(SELECT newcol FROM tempTable t WHERE t.keycol(s) = o.keycol(s))

6) DROP tempTable

Answer 2

may b ALTER TABLE HELPS lk



Sometimes we need to change the data type of a column. To do this, we use the ALTER TABLE Modify Column command. For Oracle and MySQL, the SQL syntax for ALTER TABLE Modify Column is,



For SQL Server, the syntax is,



ALTER TABLE "table_name"

ALTER COLUMN "column 1" "New Data Type"

Answer 3

definite .. its conceivable... n the interface is likewise fairly uncomplicated... (use the wizard)... n if something gets messd up.. use the inbuilt help... basically create the table then go with the fields to devise on click on the graphs n charts button on the appropriate...