SQL Problem

[STRBramley]

I have a bunch of contacts that are stored in a database in the format of contact_id, forename, surname, address, telephone etc...

They are then exported to a CSV file, called up and asked a series of questions. So the result is a CSV file with contact_id, forname, surname, address, telephone, question 1 answer, question 2 answer.

I then want to import these contacts and their answers back into my database. This is the problem. I have a table named response which contains the contact_id, question_id, the response and the response date. So for each question a contact is asked a record will be entered into this table. E.g somebody is asked 5 questions, they will be recorded into the response table 5 times.

My problem is that I don’t know how I can get the CSV imported and then the columns for the questions to be recorded into the response table. I can easily get the CSV file into SQL and the contacts into a table but I am struggling to get the responses into the response table.

So for an example if I had a row in my CSV that looks like this:

544837, Mr, John, Smith, 1 Main Street, New Road, Nottingham, 01159443556, yes, no, maybe, yes, yes

I then want to get the last 5 columns into the response table but I need to know which question they said yes no or maybe to. The columns will have heading such as ARE_YOU_SINGLE and these will relate to a question reference in my question table which contains question_id, question_ref, question, created_date, last_modified.

Does anyone have any ideas how I might be able to do this? The question responses will always be in the same order in the CSV so it might be that they are just imported blindly but this doesn't seem like a very good idea.

Any help very much appreciated.

 

[Alex H]

I'm not really sure why you need two tables? Would it not be easier just to have them all in one? Mind you, it's years since I did any SQL.

 

[STRBramley]

I'm not really sure why you need two tables? Would it not be easier just to have them all in one? Mind you, it's years since I did any SQL.

It would be easier but with the amount of data being entered it would soon become unmanageable. I need everything separate and linked together using a single link table.

 

[Chip-mk1]

I would do it with 3 tables

1) your original table that contains customer details
2) a response table that contains:
Cust_id, question, response
3) a table in the same format as the CSV you are importing.

Then after you do the import just run queries that extract the data out and put into the response table.


So for example these could be 2 of the 5 queries:

insert into response (cust_id, question, response) values (select cust_id, "are you single" as question, response_1 from temp_import_table)
insert into response (cust_id, question, response) values (select cust_id, "are you a clio owner" as question, response_2 from temp_import_table)

 

[STRBramley]

I would do it with 3 tables

1) your original table that contains customer details
2) a response table that contains:
Cust_id, question, response
3) a table in the same format as the CSV you are importing.

Then after you do the import just run queries that extract the data out and put into the response table.


So for example these could be 2 of the 5 queries:

insert into response (cust_id, question, response) values (select cust_id, "are you single" as question, response_1 from temp_import_table)
insert into response (cust_id, question, response) values (select cust_id, "are you a clio owner" as question, response_2 from temp_import_table)

Thanks for the reply. I'll try that one tonight. That's exactly how I'm doing it apart from my third csv table is a temp table.
I think that'll work but it's quite static. For instance it may be that another question is added and therefore the select and import will have to change to add this in as the csv columns cannot be added to a dynamic table, they have to be defined.

 

[Chip-mk1]

Just swap the column for question to a "question id" then and you can have an extra table that maps between question id and the details.

You could also add acceptable values to this table to use it as a lookup to ensure that all data was in expected format and acceptable values etc.

Ie so that you dont get "are you single" and a response of "Doctor" and then "whats your title" and "male" etc.

 

[STRBramley]

Cheers I'll give that a go. My problem was getting each contact into the response table 5 times (one for each question) but your method allows for an insert for each question rather than each contact.

Good stuff, thanks.
I'll let you know how I get on.

 

[Strell]

You could set up a stored procedure for that.
And then build a while loop to do it in an automated(ish) way.
Would enable it to be easier to add new questions.
How many records are we talking?

 

[STRBramley]

You could set up a stored procedure for that.
And then build a while loop to do it in an automated(ish) way.
Would enable it to be easier to add new questions.
How many records are we talking?

I'm implementing the method Chip suggested for now but that sounds like a good idea, I'll have to look up on SP's.
3000ish contacts a day and 5 questions each to start with. It could be that half a million contacts are added in one shot though.

 

[STRBramley]

Cheers I'll give that a go. My problem was getting each contact into the response table 5 times (one for each question) but your method allows for an insert for each question rather than each contact.

Good stuff, thanks.
I'll let you know how I get on.

Just to let you know I've just given this a try and it works perfectly, so thanks.
Slight alteration to the statement though

INSERT INTO response (contact_id, question_id, response)
SELECT contact_id, 1, question_1 FROM #temp


This will work for now but it definitely isn't as automated as I want it in the future.

 

[Chip-mk1]

If you are using it again and again a stored procedure and a configuration table would be a good idea.

At least you are learning along the way by the sounds of it though. SQL is quite powerful.

 

[Strell]

Yeah defo lots of help out there on Google and other forums for Sql. Used to work with SQL a fair bit. But moved to different tools now.

 

[Chip-mk1]

I still write it from time to time. Did a bit today in fact.

 

[Strell]

Only really write really quick queries now.
We migrated off it about 4 years ago.
Still nice to touch on it again every so often.