CODEWITHSUNDEEP
sql-serverbitalter-table
x Nuclear 213
x Nuclear 213

Reputation: 97

SQL Alter Column in table From Bit to Int

So What I am looking to do is alter a table and change a column from an BIT to INT. Although currently in the column there are null values I would like to change to -1 and not allow null. Since BIT only holds 0 and 1, how would I Alter the Column to an INT and Set the Null values to -1

I was thinking something along the lines of 

ALTER TABLE TABLENAME
ALTER COLUMN COLUMNAME INT SET DEFAULT -1
WHEN NULL THEN CAST(-1 AS INT)

Upvotes: 5

Views: 13046

Answers (3)

Rahul
Rahul

Reputation: 77876

proper Syntax is:

ALTER TABLE {TABLENAME} 
ALTER {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}

Do it step by step as shown below 

create table test2(id int not null identity primary key ,
 col bit);


ALTER TABLE test2
ALTER COLUMN col INT NULL --Alter to change the type to INT

alter table test2
add constraint dflt default -1 for col  --Alter to create a default value

Upvotes: 2

classic_vmk
classic_vmk

Reputation: 505

Hope the following code snippet will help you out.

--Created table for testing
CREATE TABLE Test(COl Bit)

--Insertint values
INSERT INTO Test VALUES(0)
INSERT INTO Test VALUES(NULL)
INSERT INTO Test VALUES(1)

--Change the column type
ALTER TABLE Test ALTER COLUMN COL INT

-- Update null values to -1
UPDATE Test SET COL = ISNULL(COL,-1) WHERE COL is NULL

-- Changing the column to not null
ALTER TABLE Test ALTER COLUMN COL int NOT NULL

Upvotes: 12

Old fart
Old fart

Reputation: 600

I think you will have to create a new column and update it's values in three separate update statements, then delete the old column and rename the new one

Upvotes: 0

Related Questions