title: using MS SQL I need to select into a table while casting a whole load of strings to ints! can it be done?
body: <p>Basically, I am the new IT type guy, old guy left a right mess for me! We have a MS-Access DB which stores the answers to an online questionnaire, this particular DB has about 45,000 records and each questionnaire has 220 questions. The old guy, in his wisdom decided to store the answers to the questionnaire questions as text even though the answers are 0-5 integers!</p>
  
  <p>Anyway, we now need to add a load of new questions to the questionnaire taking it upto 240 questions. The 255 field limit for access and the 30ish columns of biographical data also stored in this database means that i need to split the DB. </p>
  
  <p>So, I have managed to get all the bioinfo quite happily into a new table with: </p>
  
  <pre><code>SELECT id,[all bio column names] INTO resultsBioData FROM results;
  </code></pre>
  
  <p>this didn't cause to much of a problem as i am not casting anything, but for the question data i want to convert it all to integers, at the moment I have:</p>
  
  <pre><code>SELECT id,CInt(q1) AS nq1.......CInt(q220) AS nq220 INTO resultsItemData FROM results;
  </code></pre>
  
  <p>This seems to work fine for about 400 records but then just stops, I thought it may be because it hit something it cant convert to a integer to start with, so i wrote a little java program that deleted any record where any of ther 220 answers wasnt 0,1,2,3,4 or 5 and it still gives up around 400 (never the same record though!)</p>
  
  <p>Anyone got any ideas? I am doing this on my test system at the moment and would really like something robust before i do it to our live system!</p>
  
  <p>Sorry for the long winded question, but its doing my head in!</p>