Change type of a column with numbers from varchar to int

Question

We have two columns in a database which is currently of type varchar(16). Thing is, it contains numbers and always will contain numbers. We therefore want to change its type to integer. But the problem is that it of course already contains data.

Is there any way we can change the type of that column from varchar to int, and not lose all those numbers that are already in there? Hopefully some sort of sql we can just run, without having to create temporary columns and create a C# program or something to do the conversion and so forth... I imagine it could be pretty easy if SQL Server have some function for converting strings to numbers, but I am very unstable on SQL. Pretty much only work with C# and access the database through LINQ to SQL.

Note: Yes, making the column a varchar in the first place was not a very good idea, but that is unfortunately the way they did it.

Answer 1

The only reliable way to do this will be using a temporary table, but it will not be much SQL:

select * into #tmp from bad_table
truncate table bad_table
alter bad_table alter column silly_column int
insert bad_table
select cast(silly_column as int), other_columns
from #tmp
drop table #tmp

Answer 2

The easiest way to do this is:

alter table myTable alter column vColumn int;

This will work as long as

1. all of the data will fit inside an int
2. all of the data can be converted to int (i.e. a value of "car" will fail)
3. there are no indexes that include vColumn. If there are indexes, you will need to include a drop and create for them to get back to where you were.

Answer 3

Just change the datatype in SQL Server Management Studio.

(You may need to go to menu Tools → Options → Designers, and disable the option that prevents saving changes that re-create the table.)

Answer 4

I totally appreciate the previous answers, but also thought a more complete answer would be helpful to other searchers...

There are a couple caveats that would be helpful if you making the changes on a production type table.

1. If you have an identity column defined on the table you will have to set IDENTITY_INSERT on and off around the re-insert of data. You will also have to use an explicit column list.
2. If you want to be sure of not killing data in the database, use TRANSACTIONS around the truncate/alter/reinsert process
3. If you have a lot of data, then trying to just make the change in SQ Server Management Studio could fail with a timeout and you could lose data.

To expand the answer that @cjk gave, look at the following:

Note: 'tuc' is just a placeholder in this script for the real tablename

begin try 
  begin transaction

  print 'Selecting Data...'
  select * into #tmp_tuc from tuc

  print 'Truncating Table...'
  truncate table tuc

  alter table tuc alter column {someColumnName} {someDataType} [not null]
  ... Repeat above until done

  print 'Reinserting data...'
  set identity_insert tuc on
  insert tuc (
    <Explicit column list (all columns in table)>
  )
  select  
    <Explicit column list (all columns in table - same order as above)>
  from #tmp_tuc
  set identity_insert tuc off

  drop table #tmp_tuc
  commit
  print 'Successful!'
end try
begin catch
  print 'Error - Rollback'
  if @@trancount > 0
    rollback

  declare @ErrMsg nvarchar(4000), @ErrSeverity int
  select @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()

  set identity_insert tuc off

  RAISERROR(@ErrMsg, @ErrSeverity, 1)
end catch