CODEWITHSUNDEEP
sqlsql-serveralter-table
blueberryfields
blueberryfields

Reputation: 50378

is there an easy to use formula for switching the datatype on a table column from int to bit?

I'm looking at a sql server table, and one of the columns seems to have been setup to be of type INT NULL when it's really better off as a BIT NOT NULL.

Is there an easy formula or shortcut for switching the type?

My current strategy is:

this seems complicated but, really, should be automatable - so i'm wondering if anyone's automated it yet.

Upvotes: 0

Views: 53

Answers (1)

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

Update MyTable set MyColumn = 0 where MyColumn is null

Assuming you want null to be false

Alter table MyTable alter column MyColumn bit not null

Will do the job, given all non-zero values are to be treated as true.

Course you were going to backup before you did any of this weren't you. :)

Upvotes: 2

Related Questions