Modifying a column type with data, without deleting the data

Question

I have a column which I believe has been declared wrongly. It contains data and I do not wish to lose the data.

I wish to change the definition from varchar(max) to varchar(an integer). I was under the impression I cannot just alter the column type?

Is the best method to create a temp column, "column2", transfer the data to this column, from the column with the problematic type, delete the problem column and then rename the temp column to the original problematic column?

If so, how do I copy the values from the problem column to the new column?

EDIT: For anyone with same problem, you can just use the ALTER statements.

Answer 1

As long as the data types are somewhat "related" - yes, you can absolutely do this.

You can change an INT to a BIGINT - the value range of the second type is larger, so you're not in danger of "losing" any data.

You can change a VARCHAR(50) to a VARCHAR(200) - again, types are compatible, size is getting bigger - no risk of truncating anything.

Basically, you just need

ALTER TABLE dbo.YourTable
ALTER COLUMN YourColumn VARCHAR(200) NULL

or whatever. As long as you don't have any string longer than those 200 characters, you'll be fine. Not sure what happens if you did have longer strings - either the conversion will fail with an error, or it will go ahead and tell you that some data might have been truncated. So I suggest you first try this on a copy of your data :-)

It gets a bit trickier if you need to change a VARCHAR to an INT or something like that - obviously, if you have column values that don't "fit" into the new type, the conversion will fail. But even using a separate "temporary" new column won't fix this - you need to deal with those "non-compatible" cases somehow (ignore them, leave NULL in there, set them to a default value - something).

Also, switching between VARCHAR and NVARCHAR can get tricky if you have e.g. non-Western European characters - you might lose certain entries upon conversion, since they can't be represented in the other format, or the "default" conversion from one type to the other doesn't work as expected.

Answer 2

Calculate the max data length store int that column of that table.

Select max(len(fieldname)) from tablename

Now you can decrease the size of that column up to result got in previous query.

ALTER TABLE dbo.YourTable
ALTER COLUMN YourColumn VARCHAR(200) NULL