How to change column datatype in SQL database without losing data

Question

I have SQL Server database and I just realized that I can change the type of one of the columns from int to bool    How can I do that without losing the data that is already entered into that table

User · Answer

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

User · Answer

Alter column data type with check type of column    IF EXISTS         SELECT 1        FROM   sys columns        WHERE  NAME    YourColumnName                AND  object id    OBJECT ID  dbo YourTable                 AND TYPE NAME system type id     int           ALTER TABLE dbo YourTable ALTER COLUMN YourColumnName BIT

User · Answer

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

User · Answer

I can modify the table field s datatype  with these following query  and also in the Oracle DB   ALTER TABLE table name MODIFY column name datatype

User · Answer

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

User · Answer

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

User · Answer

for me   in sql server 2016  I do it like this   To rename column Column1 to column2   EXEC sp rename  dbo T Table1 Column1    Column2    COLUMN     To modify column Type from string to int   Please be sure that data are in the correct format   ALTER TABLE dbo T Table1 ALTER COLUMN Column2  int

User · Answer

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

User · Answer

Replace datatype without losing data  alter table tablename modify columnn  newdatatype size

User · Answer

ALTER TABLE tablename ALTER COLUMN columnname columndatatype size    Note  if there is a size of columns  just write the size also

User · Answer

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

[sql-server] How to change column datatype in SQL database without losing data

Examples related to sql-server

Examples related to sql-server-2008

Examples related to alter