CODEWITHSUNDEEP
sqlms-accesscastingms-access-2007
Ben
Ben

Reputation: 5715

using MS SQL I need to select into a table while casting a whole load of strings to ints! can it be done?

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!

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.

So, I have managed to get all the bioinfo quite happily into a new table with: 

SELECT id,[all bio column names] INTO resultsBioData FROM results;

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:

SELECT id,CInt(q1) AS nq1.......CInt(q220) AS nq220 INTO resultsItemData FROM results;

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!)

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!

Sorry for the long winded question, but its doing my head in!

Upvotes: 1

Views: 485

Answers (3)

HansUp
HansUp

Reputation: 97131

I'm unsure whether you're talking about doing the data transformation in Access or SQL Server. Either way, since you're redesigning the schema, now is the time to consider whether you really want resultsItemData table to include 200+ fields, from nq1 through nq220 (or ultimately nq240). And any future question additions would require changing the table structure again.

The rule of thumb is "columns are expensive; rows are cheap". That applies whether the table is in Access or SQL Server.

Consider one row per id/question combination.

id q_number answer
 1      nq1      3
 1      nq2      1

I don't understand why your current approach crashes at 400 rows. I wouldn't even worry about that, though, until you are sure you have the optimal table design.

Edit: Since you're stuck with the approach you described, I wonder if it might work with an "append" query instead of a "make table" query. Create resultsItemData table structure and append to it with a query which transforms the qx values to numeric. 

INSERT INTO resultsItemData (id, nq1, nq2, ... nq220)
SELECT id, CInt(q1), CInt(q2), ... CInt(q220) FROM results;

Upvotes: 2

Ben
Ben

Reputation: 5715

Just wrote a small java program in the end that created the new table and went through each record individually casting the fields to integers, takes about an hour and a half to do the whole thing though so i am still after a better solution when i come to do this with the live system.

Upvotes: 0

James Johnson
James Johnson

Reputation: 46067

Try this solution:

select * into #tmp from bad_table
truncate table bad_table
alter bad_table alter column silly_column int
insert bad_table
select cast(silly_column as int), other_columns
from #tmp
drop table #tmp

Reference: Change type of a column with numbers from varchar to int

Upvotes: 0

Related Questions