CODEWITHSUNDEEP
sql-servertype-conversion
user811433
user811433

Reputation: 4149

How to change datatype of a column containing data in sql server?

I have a table in my SQL Server DB. 

CREATE TABLE [dbo].[Table2]([col3] [varchar](50) NULL, [col4] [varchar](50) NULL, [col5] [char](1) NULL)

Following is the data in the table:

col3    col4    col5
sad     asdf    c
df      sdfg    b

Now I want to change the datatype of 'col5' to INT. Tried this:

alter table [dbo].[TABLE2] alter column [col5] int null;

Encountered this error:

Conversion failed when converting the varchar value 'c' to data type int.
The statement has been terminated.

After changing the datatype to INT - I want to change 'c' to 100 and 'b' to 101.

How can I change the datatype and the data? Please point me in the right direction.

Upvotes: 1

Views: 3304

Answers (2)

Jairon Alava
Jairon Alava

Reputation: 71

First you must increase your col5:

alter table [dbo].[TABLE2] alter column [col5] varchar(10) null;

Then UPDATE your data to something such as numbers. Maybe:

UPDATE Table set col5 = '100' where col5='c'

Upvotes: 2

Devasayal
Devasayal

Reputation: 40

Alter column - wouldn't work for type conversions from varchar to int/long/etc.,

  • You will have to create a new int column
  • Then copy all the existing data from col5 to new int column
  • and then delete the col5 column from TABLE2

There could be smarter ways. However, this serves the purpose i guess.

Upvotes: 1

Related Questions