Convert data type of a column

Background:
This post will cover few options to convert a column data type without loosing actual data. All the scripts will work fine based on the assumption that value in column can actually be converted and data will not be lost. For example, INT to VARCHAR conversion is relatively straight forward, no possible data loose. Whereas, VARCHAR to INT might fail if that column has non-numeric value or has value which is too big (INT column range is -2,147,483,648 to 2,147,483,647).


Option-1 is simpler, actual data type change statement is one line. Option-2 is much complicated approach where a dummy column is used. I have added Option-2 for two reasons, 1) that's the one I created first 2) in some scenarios Option-2 might help others to consider as base approach and build deployment/database structure modification script on top of that.


Option-1: using ALTER COLUMN



DECLARE 
@tableName VARCHAR(100) = 'wk_fact_ctr_transaction',
@targetColumnName VARCHAR(100) = 'account',
@targetColumnDataType VARCHAR(100) = 'INT',
@columnExists SMALLINT = 0,
@sql NVARCHAR(MAX)
;


-- Check column exists in the table
SET @sql = 'SELECT @cnt=COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '''+@tableName+''' AND COLUMN_NAME = '''+@targetColumnName+'''';
PRINT @sql;
EXECUTE sp_executesql @sql, N'@cnt SMALLINT OUTPUT',@columnExists OUTPUT;


IF @columnExists IS NOT NULL AND @columnExists > 0
BEGIN
PRINT 'Column exists in the table';
BEGIN TRY
SET @sql = 'ALTER TABLE '+@tableName+' ALTER COLUMN '+@targetColumnName+' '+@targetColumnDataType;
EXECUTE(@sql);
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;


SELECT 
@ErrorMessage = ERROR_MESSAGE() + ' - COLUMN CONVERSION NOT POSSIBLE.',
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
END
ELSE
PRINT 'Column does not exist in the table';

Option-2: ALTER COLUMN by introducing a dummy column



DECLARE 
@tableName VARCHAR(100) = 'wk_fact_ctr_transaction',
@targetColumnName VARCHAR(100) = 'account',
@dummyColumnName VARCHAR(100) = 'account_temp',
@targetColumnDataType VARCHAR(100) = 'VARCHAR(100)',
@dummyColumnDataType VARCHAR(100) = 'INT',
@columnExists SMALLINT = 0,
@sql NVARCHAR(MAX)
;


SET @sql = 'SELECT @cnt=COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '''+@tableName+''' AND COLUMN_NAME = '''+@targetColumnName+'''';
PRINT @sql;
EXECUTE sp_executesql @sql, N'@cnt SMALLINT OUTPUT',@columnExists OUTPUT;


IF @columnExists IS NOT NULL AND @columnExists > 0
BEGIN
PRINT 'Column exists in the table';
BEGIN TRY
SET @sql = 'ALTER TABLE '+@tableName+' ADD '+@dummyColumnName+' '+@dummyColumnDataType;
EXECUTE(@sql);


SET @sql = 'UPDATE '+@tableName+' SET '+@dummyColumnName+'='+@targetColumnName;
EXECUTE(@sql);


SET @sql = 'ALTER TABLE '+@tableName+' DROP COLUMN '+@targetColumnName;
EXECUTE(@sql);


SET @sql = 'ALTER TABLE '+@tableName+' ADD '+@targetColumnName+' '+@targetColumnDataType;
EXECUTE(@sql);


SET @sql = 'UPDATE '+@tableName+' SET '+@targetColumnName+'='+@dummyColumnName;
EXECUTE(@sql);


SET @sql = 'ALTER TABLE '+@tableName+' DROP COLUMN '+@dummyColumnName;
EXECUTE(@sql);
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;


SELECT 
@ErrorMessage = ERROR_MESSAGE() + ' - COLUMN CONVERSION NOT POSSIBLE.',
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();

RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
END
ELSE
PRINT 'Column does not exist in the table';