CODEWITHSUNDEEP
sql-serversql-server-2008alter
Ivan Stoyanov
Ivan Stoyanov

Reputation: 5482

How to change column datatype in SQL Server database without losing data?

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?

Upvotes: 223

Views: 749493

Answers (11)

Ebrahim Sabeti
Ebrahim Sabeti

Reputation: 51

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

Upvotes: 4

MNF
MNF

Reputation: 717

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;

Upvotes: 4

Xyed Xain Haider
Xyed Xain Haider

Reputation: 498

ALTER TABLE tablename
ALTER COLUMN columnname columndatatype(size)

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

Upvotes: 28

Sathish
Sathish

Reputation: 2099

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.

Upvotes: 24

NullException
NullException

Reputation: 179

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)

Upvotes: 11

Ismayil S
Ismayil S

Reputation: 243

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;

Upvotes: -3

saktiprasad swain
saktiprasad swain

Reputation: 340

Go to Tool-Option-designers-Table and Database designers and Uncheck Prevent saving optionenter image description here

Upvotes: 4

user7743491
user7743491

Reputation: 11

Replace datatype without losing data

alter table tablename modify columnn  newdatatype(size);

Upvotes: -5

Hardik
Hardik

Reputation: 33

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

Upvotes: 0

marc_s
marc_s

Reputation: 754268

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

Upvotes: 363

Philippe Leybaert
Philippe Leybaert

Reputation: 171744

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.

Upvotes: 8

Related Questions