August 4, 2010 at 9:44 am
I am trying to get the questionID from two different Select statements and
then insert into a table one by one.
DECLARE @NewID INT
This one is inserting a new surveyID, Output is SurveyID
insert into survey(title,description, surveystatus,CreatedBy,date )
values('New Survey','New Survey',1,'test',Getdate())
SELECT @NewID = SCOPE_IDENTITY()
Copying the questions with the new surveyID
INSERT SurveyQuestions(surveyid, questions,answertype)
SELECT @NewID ,questions,answertype
FROM SurveyQuestions
WHERE surveyid='81'
The problem is below here. I want to get the value of the questionId of the
first select statement then the value of the questionID of the second select
statement and insert into the table SurveyChoices one by one.
Both the select statements can have 1 or 2 or 3 rows or more but the both the
select statements will have exact number of rows.
select QuestionId from surveyquestions where surveyid=@NewID and answertype
<> 'T'
select QuestionId from surveyquestions where surveyid='81' and answertype <>
'T'
Here i am using the insert statement using the value form First select
statement and second select statement
INSERT Surveychoices(QuestionId,choice)
SELECT questionID,choice((This is the value of the First select statement.)
FROM Surveychoices
WHERE questionid=questionID(This is the value of the second select statement.)
August 4, 2010 at 12:57 pm
August 4, 2010 at 1:51 pm
This is how the data looks like before i insert the new survey automatically.
Survey
surveyid= 1 (Auto number)
title= test title
description= test title
surveystatus=1
CreatedBy=test
date= today's date
SurveyQuestions
Questionsid= 1 (autonumber)
SurveyID=1
Questions="Good Work"
AnswerType= M
Questionsid= 2 (autonumber)
SurveyID=1
Questions="bad Work"
AnswerType= S
SurveyChoices
ChoiceID=1(autonumber)
QuestionID=1
Choice=Good
ChoiceID=2(autonumber)
QuestionID=1
Choice=Bad
ChoiceID=3(autonumber)
QuestionID=2
Choice=Excellent
ChoiceID=4(autonumber)
QuestionID=2
Choice=Poor
The Data Should look like this when everything is inserted
Survey
surveyid= 1 (Auto number)
title= test title
description= test title
surveystatus=1
CreatedBy=test
date= today's date
surveyid= 2 (Auto number)
title= yahoo title
description= Yahootitle
surveystatus=1
CreatedBy=test
date= today's date
SurveyQuestions
Questionsid= 1 (autonumber)
SurveyID=1
Questions="Good Work"
AnswerType= M
Questionsid= 2 (autonumber)
SurveyID=1
Questions="bad Work"
AnswerType= S
Questionsid= 3 (autonumber)
SurveyID=2
Questions="Good Work"
AnswerType= M
Questionsid= 4 (autonumber)
SurveyID=2
Questions="bad Work"
AnswerType= S
SurveyChoices
ChoiceID=1(autonumber)
QuestionID=1
Choice=Good
ChoiceID=2(autonumber)
QuestionID=1
Choice=Bad
ChoiceID=3(autonumber)
QuestionID=2
Choice=Excellent
ChoiceID=4(autonumber)
QuestionID=2
Choice=Poor
ChoiceID=5(autonumber)
QuestionID=3
Choice=Good
ChoiceID=6(autonumber)
QuestionID=3
Choice=Bad
ChoiceID=7(autonumber)
QuestionID=4
Choice=Excellent
ChoiceID=8(autonumber)
QuestionID=4
Choice=Poor
Inserting into the survey and surveyquestions is working fine. I am not able to insert into surveychoices
I want to copy the option(Table SurveyChoices) Good, bad, excellent, poor of questionid 1 and 2 and assign the 4 options(good, bad, poor, excellent) with the new questionID 3 and 4.
August 4, 2010 at 4:02 pm
It's still not exactly clear what you want (and you didn't follow the instruction in the link in my signature.) I don't see what the "Choice" has to do with either the Survey table or the SurveyQuestions table. In looking at the information you have provided it would seem that the SurveyChoices insert(s) is an arbitrary number of arbitrary values based on the most recently inserted entry in SurveyQuestions.
August 4, 2010 at 5:10 pm
Survey Table has the Survey. Every survey has questions attached to it(SurveyQuestions). Every questions has choices attached to it(SurveyChoices). Tables attachment file
I am trying to copy a previous survey(all of its questions, choices attached to its questions with new ID's)
So a user selects a survey he wants to copy.
1)I make a new survey with new surveyID in the survey table.
2)Then i copy all questions related to the survey that the user has selected with the newsurevyID that i have created.
3)Then i copy all the choices related to the questions that the user has selected with the new questionID of the new survey.
The third part i am not able to do it.
August 4, 2010 at 7:05 pm
If you provide data in the correct format (see the link in my signature) I could show you how to write the query to do that, but based on how I am understanding your intent, my opinion is that it is poor design. Sometimes we are stuck with bad design because it's out of our control, but if you can fix it this is the time to do so before a bunch of time is invested in writing procs and whatnot.
If all you're doing is creating copies of the same survey, your data is redundant, which is undesirable. Every time the survey is going to have the same questions 1-N and there is no point of having a table that defines questions 1-N over and over again for as many times as the survey is used.
I would change the use of the Survey table to apply to distinct sets of questions and answers. The SurveyQuestions table would have one set of questions for each unique survey. The SurveyChoices table would have a set of answers specific to each one of those questions. Then I would add the following type of structure:
CREATE TABLE SurveyTaken
(SurveyTakenID INT IDENTITY PRIMARY KEY
,WhoTookIt NVARCHAR(200)
,WhenTheyTookIt DATETIME
,WhatSurveyTheyTook INT) -- (FK) (if you have more than one distinct survey)
CREATE TABLE SurveyTakenResults
(SurveyTakenResultID INT IDENTITY PRIMARY KEY
,SurveyTakenID INT -- (FK)
,QuestionID INT -- (FK)
,ChoiceID INT) -- (FK)
With this, not only would I be able to accurately and efficiently store survey results for any number of respondents, but I would also be able to easily run statistics on how different questions were answered in what way, etc.
August 5, 2010 at 4:41 pm
I had to redesign the database. I took your help for the tables. Thanks:-)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply