How to change column datatype in SQL database without losing data

Question

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).

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..

ALTER TABLE tablename
ALTER COLUMN columnname columndatatype(size)

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

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.

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

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

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

if you use MySQL; you should try this script:

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)

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.

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