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?
This question is related to
sql-server
sql-server-2008
alter
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)
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
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;
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...
ALTER TABLE tablename
ALTER COLUMN columnname columndatatype(size)
Note: if there is a size of columns, just write the size also.
Replace datatype without losing data
alter table tablename modify columnn newdatatype(size);
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;
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.
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.
Source: Stackoverflow.com