SQL Server: Converting Column type from INT to BIT

The following query creates a temporary column (INT) copies the data to it and recreates the original column as BIT and copies the data back.

Please note that column order will not be retained. If you need the same column order, go to Design view of the table and drag the column to the location you want and save.

Change the value in variable @name and @tableName. The query takes care of the rest.

DECLARE @name NVARCHAR(255), @tempName NVARCHAR(255), 
        @tableName NVARCHAR(255), @sql NVARCHAR(max);

SET @name = 'ColumnName';
SET @tempName = @name +'1';
SET @tableName = 'TableName';

--SELECT @name, @tempName, @tableName

-- create new temp column
SET @SQL = 'ALTER TABLE [dbo].[' + @tableName + '] ADD ' + @tempName + ' INT';
EXEC sp_executesql @sql


--copy data in temp column
SET @SQL = 'UPDATE [dbo].[' + @tableName + '] SET ' + @tempName + ' = ' + @name;
EXEC sp_executesql @sql

--drop column which you want to modify
SET @SQL = 'ALTER TABLE [dbo].[' + @tableName + '] DROP COLUMN ' + @name;
EXEC sp_executesql @sql

--create again that column with bit type
SET @SQL = 'ALTER TABLE [dbo].[' + @tableName + '] ADD ' + @name +' BIT';
EXEC sp_executesql @sql

--copy data back
SET @SQL = 'UPDATE [dbo].[' + @tableName + '] SET ' + @name + ' = ' + @tempName;
EXEC sp_executesql @sql

--drop temp column
SET @SQL = 'ALTER TABLE [dbo].[' + @tableName + '] DROP COLUMN ' + @tempName;
EXEC sp_executesql @sql