Force INT data type in my sql query
I am trying to import data from multiple old tables into one new table. I'm getting some constraint error when trying to do this in SSMS Import tool. The only thing I can think of is that the one field in the query is identifying the the field value as a varchar(1) instead of an integer (which is what the field data type is in the new table). How do I force this part of my query to recognize the value as an INT instead of varchar(1)?
intCATEGORYID =
CASE
WHEN intID LIKE 'IN%' THEN '3'
WHEN intID LIKE 'FD%' THEN '2'
WHEN intID LIKE 'DD%' THEN '4'
WHEN intID LIKE 'TC%' THEN '5'
ELSE '1'
END,
intCATEGORYID =
CASE
WHEN intID LIKE 'IN%' THEN '3'
WHEN intID LIKE 'FD%' THEN '2'
WHEN intID LIKE 'DD%' THEN '4'
WHEN intID LIKE 'TC%' THEN '5'
ELSE '1'
END,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Scott Pletcher
But that should not be the problem. A char/varchar should load fine into an int column, as long as all the data is valid for the int data type.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
you can do as in suggestion 1, or just remove the quotes:
intCATEGORYID =
CASE
WHEN intID LIKE 'IN%' THEN 3
WHEN intID LIKE 'FD%' THEN 2
WHEN intID LIKE 'DD%' THEN 4
WHEN intID LIKE 'TC%' THEN 5
ELSE 1
END,
You may get tinyint or some other int version if you just remove the quotes. If you want to get *exactly* an *int*, you must CAST to it.
ASKER
That made it work...
Interesting. Glad it worked :-) .