如何更改包含sql server中数据的列的数据类型？

I have a table in my SQL Server DB.

我的SQL Server DB中有一个表。

CREATE TABLE [dbo].[Table2]([col3] [varchar](50) NULL, [col4] [varchar](50) NULL, [col5] [char](1) NULL)

Following is the data in the table:

以下是表中的数据:

col3    col4    col5
sad     asdf    c
df      sdfg    b

Now I want to change the datatype of 'col5' to INT. Tried this:

现在我想将'col5'的数据类型更改为INT。试过这个:

alter table [dbo].[TABLE2] alter column [col5] int null;

Encountered this error:

遇到这个错误:

Conversion failed when converting the varchar value 'c' to data type int.
The statement has been terminated.

After changing the datatype to INT - I want to change 'c' to 100 and 'b' to 101.

将数据类型更改为INT后 - 我想将“c”更改为100,将“b”更改为101。

How can I change the datatype and the data? Please point me in the right direction.

如何更改数据类型和数据?请指出我正确的方向。

2 个解决方案

#1

First you must increase your col5:

首先,你必须增加你的col5:

alter table [dbo].[TABLE2] alter column [col5] varchar(10) null;

Then UPDATE your data to something such as numbers. Maybe:

然后将数据更新为数字等内容。也许:

UPDATE Table set col5 = '100' where col5='c'

#2

Alter column - wouldn't work for type conversions from varchar to int/long/etc.,

更改列 - 不适用于从varchar到int / long / etc的类型转换,

You will have to create a new int column

您将不得不创建一个新的int列

Then copy all the existing data from col5 to new int column

然后将所有现有数据从col5复制到新int列

and then delete the col5 column from TABLE2

然后从TABLE2中删除col5列

There could be smarter ways. However, this serves the purpose i guess.

可能有更聪明的方法。但是,这符合我的目的。

#1