title: Error converting data type nvarchar to float - nvarchar to float to int
body: <p>I don't have that much experience with writing SQL queries and I have hit upon a problem. I have read in a table of data into a temporary table (#Temp_Results) and need to change the format of various columns before moving the data to the end table. 
  What Im trying to do below is take a column (Oil2) that is an nvarchar and convert it to a tinyint and put the result into a new column (Oil4) then drop Oil2 - I realise I will loose decimal places but thats not a problem. The CASE statement is designed to capture anything that is not a number, seeing as the original datatype is nvarchar there could be anything in there and I'm only interested in the numbers.</p>
  
  <p>However when I run the code I get 'Error converting data type nvarchar to float' pointing towards the 'UPDATE' line of code and I cant figure out how to get round it.</p>
  
  <p>Can any of you guys spot my rookie mistake?</p>
  
  <pre><code>ALTER TABLE tempdb..#Temp_Results       /*Add new column with datatype of tinyint*/ 
  ADD Oil4 tinyint
  GO
  
  UPDATE tempdb..#Temp_Results            
  SET tempdb..#Temp_Results.Oil4 = CASE 
  WHEN ISNUMERIC(tempdb..#Temp_Results.Oil2)=1
  THEN CAST(ROUND(CAST(tempdb..#Temp_H_Results.Oil2 as float), 0) AS tinyint)
  ELSE NULL
  END
  
  ALTER TABLE tempdb..#Temp_H_Results     /*Drop redundant column of data in wrong (nvarchar) format*/
  DROP COLUMN Oil2
  Go
  </code></pre>