We help IT Professionals succeed at work.

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,
Comment
Watch Question

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
intCATEGORYID =
CAST(     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  AS int),  
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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,

Open in new window

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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.
Lee R Liddick JrReporting Analyst

Author

Commented:
That made it work...
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
Interesting.  Glad it worked :-) .

Explore More ContentExplore courses, solutions, and other research materials related to this topic.