CODEWITHSUNDEEP
sqlsql-serversql-server-2014
dantey89
dantey89

Reputation: 2287

SQL server convert int field to bit

I have a big database and I should to normalize it. One of the table contains field with type "integer" but it contains only 1 and 0 values. So it is good reason to convert this field to bit type. But when I try to save changes in SQL Server Management Studio it tells me that I can't do it. Also I have many field with values like nvarchar that should be converted to int or float that should be converted to int too.

Moreover I should create migration scripts for all changes so I can update real database without loosing data. Maybe somebody knows useful utility for this?

EDIT: It tells me that I can't update unable without drop it. And I want to update table without losing any data.

SQL version 2014

Upvotes: 1

Views: 3269

Answers (5)

NikhilSN
NikhilSN

Reputation: 43

Simply Use TSql script to modify the table rather than using the designer

ALTER TABLE YourTableNameHere
ALTER COLUMN YourColumnNameHere INT

If you are using sql Server then you might wanna generate script for the table before altering the table so that you dont loose any data ..and you can simply retrieve everything using the script

Upvotes: 0

dantey89
dantey89

Reputation: 2287

After reading of all comments and posts I found solution in building procedure which will convert passed table and column in required. So I wrote this function

IF EXISTS (select * from dbo.sysobjects where id = object_id(N'IntToBit') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE IntToBit
GO


IF OBJECT_ID('convertion_table', 'U') IS NOT NULL
DROP TABLE dbo.convertion_table;
go

CREATE  TABLE dbo.convertion_table
(
    bitTypeColumn bit NOT NULL DEFAULT 0,
    intTypeColumnt integer ,
)
go

CREATE procedure IntToBit
@table nvarchar(150),
@column nvarchar(150)

AS
begin

DECLARE @sql nvarchar(4000)
SELECT @sql ='
    --copy data to temp table
    INSERT INTO convertion_table (bitTypeColumn)
    SELECT '+@column +'
    FROM ' +@table+'

    --Drop column which you want to modify
    Alter Table ' +@table+'
    Drop Column '+@column +'


    --Create again that column with bit type
    Alter Table ' +@table+'
    Add '+@column +' bit NOT NULL DEFAULT(0)

     --copy date back
    INSERT INTO '+@table+'('+@column+')
    SELECT bitTypeColumn
    FROM convertion_table

    --cleare temp table
    --DELETE bitTypeColumn FROM convertion_table 
    '
    exec sp_executesql @sql
end
GO

and then call it passing field and table name :

exec dbo.IntToBit @table = 'tbl_SystemUsers', @column='intUseLogin';

Special thanks to Chris K and Hitesh Thakor

Upvotes: 0

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

To generate migration script you don't need a special utility, SSMS does it pretty well.

Right-click the table in SSMS object explorer. Choose Design item in the context menu. Change the type of the column. In the main menu Table Designer choose item Generate Change Script. Save the generated script to a file, review it and make sure you understand each line in it before you run it on a production system. Adjust the script if needed.

On the other hand, to change the column type from int to bit you can use the ALTER TABLE statement:

ALTER TABLE dbo.TableName
ALTER COLUMN ColumnName bit NOT NULL

Before running this you should check that actual int values are indeed only 0 and 1.

Upvotes: 0

Chris J
Chris J

Reputation: 936

Here's how to add a new column to a table, set that column to the old column and then remove the old column

CREATE TABLE #test
(inttest int

)

Insert [#test]
        ( [inttest] )
Values  ( 0  
          )


Insert [#test]
        ( [inttest] )
Values  ( 1  
          )

Alter Table [#test] Add bittest bit

Update [#test] Set bittest=inttest

Alter Table [#test] Drop Column [inttest]

SELECT * FROM [#test] [T]

Upvotes: 0

Hitesh Thakor
Hitesh Thakor

Reputation: 471

    ---Create one Temp. Column
    Alter Table [dbo].[Demo2]
    Add tempId int

    GO

    --Copy Data in temp. Coulmn
    Update [dbo].[Demo2] set tempId=Id


    --Drop column which you want to modify
    Alter Table [dbo].[Demo2]
    Drop Column Id
    Go

    --Create again that column with bit type
    Alter Table [dbo].[Demo2]
    Add Id bit
    GO
    --copy date back
    Update [dbo].[Demo2] set Id=tempId

    --drop temp column
    Alter Table [dbo].[Demo2]
    Drop Column tempId

    Go

Upvotes: 3

Related Questions