如何在不丢失数据的情况下更改SQL数据库中的列数据类型

I have SQL Server database and I just realized that I can change the type of one of the columns from int to bool.

我有一个SQL Server数据库，我刚刚意识到我可以改变从int到bool的一个列的类型。

How can I do that without losing the data that is already entered into that table?

如何才能在不丢失已输入到该表中的数据的情况下做到这一点呢?

9 个解决方案

#1

264

You can easily do this using the following command. Any value of 0 will be turned into a 0 (BIT = false), anything else will be turned into 1 (BIT = true).

使用下面的命令可以很容易地做到这一点。任何0的值都将变成0 (BIT = false)，任何其他值都将变成1 (BIT = true)。

ALTER TABLE dbo.YourTable
   ALTER COLUMN YourColumnName BIT

The other option would be to create a new column of type BIT, fill it from the old column, and once you're done, drop the old column and rename the new one to the old name. That way, if something during the conversion goes wrong, you can always go back since you still have all the data..

另一种方法是创建一个新的类型的列，从旧的列中填充它，一旦完成，删除旧的列并将新列重命名为旧的名称。这样的话，如果转换过程中发生了错误，您可以始终返回，因为您仍然拥有所有的数据。

#2

If it is a valid change.

如果这是一个有效的改变。

you can change the property.

你可以改变属性。

Tools --> Options --> Designers --> Table and Database designers --> Uncheck --> Prevent saving changes that required table re-creation.

工具—>选项—>设计器—>表和数据库设计器—> Uncheck—>防止保存需要重新创建表的更改。

Now you can easily change the column name without recreating the table or losing u r records.

现在，您可以轻松地更改列名，而无需重新创建表或丢失u r记录。

#3

ALTER TABLE tablename
ALTER COLUMN columnname columndatatype(size)

Note: if there is a size of columns, just write the size also.

注意:如果有一个列的大小，也要写这个大小。

#4

Why do you think you will lose data? Simply go into Management Studio and change the data type. If the existing value can be converted to bool (bit), it will do that. In other words, if "1" maps to true and "0" maps to false in your original field, you'll be fine.

为什么你认为你会丢失数据?只需进入Management Studio并更改数据类型。如果现有的值可以转换为bool(位)，它就会这样做。换句话说，如果“1”映射到true，“0”映射到原始字段中的false，那么就可以了。

#5

Go to Tool-Option-designers-Table and Database designers and Uncheck Prevent saving option

转到“工具选项-设计器-表和数据库设计器”，取消“阻止保存”选项

#6

if you use T-SQL(MSSQL); you should try this script:

如果你使用t - sql(该软件);你应该试试这个剧本:

ALTER TABLE [Employee] ALTER COLUMN [Salary] NUMERIC(22,5)

if you use MySQL; you should try this script:

如果使用MySQL;你应该试试这个剧本:

ALTER TABLE [Employee] MODIFY COLUMN [Salary] NUMERIC(22,5)

if you use Oracle; you should try this script:

如果你使用甲骨文;你应该试试这个剧本:

ALTER TABLE [Employee] MODIFY [Salary] NUMERIC(22,5)

#7

In compact edition will take size automatically for datetime data type i.e. (8) so no need to set size of field and generate error for this operation...

在紧凑版中，datetime数据类型(8)的大小将自动进行调整，因此不需要设置字段的大小并为此操作生成错误……

#8

-2

I can modify the table field's datatype, with these following query: and also in the Oracle DB,

我可以通过以下查询修改表字段的数据类型:以及在Oracle DB中，

ALTER TABLE table_name
MODIFY column_name datatype;

#9

-3

Replace datatype without losing data

在不丢失数据的情况下替换数据类型

alter table tablename modify columnn  newdatatype(size);

#1

264