• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Rob Spoor
  • Devaka Cooray
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • Tim Holloway
Bartenders:
  • Jj Roberts
  • Al Hobbs
  • Piet Souris

Splitting Values

 
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
We're trying to load a text file into a SQL Server database and ran into a bit of a challenge. The text file contains answers to questions on a questionnaire and each line represents a single response, so it'd look something like this:



The trouble we face is that, in some cases, the questions may be "check all that apply", as opposed to just "check one answer". Those types of questions can have multiple answers and the file we receive notes them like this:



In this case, the user selected both answers 4 and 5 for question 456. In the end, we'd like to have this represented by two rows in the database, one with answer #4 and one with answer #5.

Anyone have any slick ideas for how we might make that happen using SQL?
 
author
Posts: 4278
34
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Normalize the data so that the answers and the questions are in separate tables. For example, if a question has one answer, that would be 2 rows: one in the question table and one in the answer table. If a question has 2 answers, that would 3 rows: one in the question table and two in the answer table.
 
Corey McGlone
Ranch Hand
Posts: 3271
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Scott Selikoff wrote:Normalize the data so that the answers and the questions are in separate tables. For example, if a question has one answer, that would be 2 rows: one in the question table and one in the answer table. If a question has 2 answers, that would 3 rows: one in the question table and two in the answer table.



That's exactly what we're shooting for. We have a question table and we have an answer table, set up just as you've described.

The trouble is that we're trying to load a text file in which two answers are represented by a single row into that table structure. We're using SSIS to load the data so we're essentially using SQL. We've considered using a cursor to step over the rows in the file and processing each one but the amount of data we're working with is so large that it prevents us from using a cursor - it's just too slow.

What we have now is a "load table" that looks like this:



...and we want to translate that into this structure:

 
Scott Selikoff
author
Posts: 4278
34
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So this issue isn't really about normalization, its about performance tuning.

You can build a stored procedure insert the data from a single string, although I find them too unruly and frustrating to maintain. You can also drop all your indexes/foreign key constraints and perform a bulk load of the data, then reinstate the restrictions. For large data sets this can often be helpful. Otherwise, there's not much you can do. The data has to get into the database somehow.
 
You showed up just in time for the waffles! And this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic