1

I have tables named Categories, Questions and Selections. The relationship of the tables are: there can be 1 or more Selections in a Question and there can be one or more Questions in a Category.

The table columns are the following:

Categories
- CategoryID (pk)
- Name

Questions
- QuestionID (pk)
- Question
- CategoryID (fk)

Selections
- SelectionID (pk)
- Selection
- QuestionID (fk)

I want to convert this code from C# to SQL:

private int fromCategoryID = 1; private int toCategoryID = 2; Category cat1 = new Category(); //this is the category we will get questions from. Category cat2 = new Category(); //this is the category we copy the questions to. // code to populate the 2 category instances and their children (Questions) and // children's children (Selections) removed for brevity. // copy questions and selections from cat1 to cat2 foreach(Question q from cat1.Questions) { Question newQuestion = new Question(); newQuestion.Question = q.Question; foreach(Selection s in q.Selections) { Selection newSelection = new Selection(); newSelection.Selection = s.Selection; q.Selections.Add(newSelection); } cat2.Questions.Add(newQuestion); }

How can this be done in SQL?

Improve this question
3
  • What kind of PK we're talking about here? Commented Jun 11, 2009 at 5:50
  • PK data type you mean? it's an identity seeded PK (int). Commented Jun 11, 2009 at 6:29
  • SQL Server Developer Edition 2005 Commented Jun 14, 2009 at 22:31

3 Answers 3

Reset to default
2

You will need 2 inserts if you want to bring both the Questions and the Selections across. Based on the assumption that Question is unique within a category this will do what you want.

declare @FromCategoryId int declare @NewCategoryId int set @NewCategoryId = 3 set @FromCategoryId = 2 insert into Questions select Question, @NewCategoryId from Questions where CategoryId = @FromCategoryId insert into Selections select S.Selection, QNew.QuestionId from Questions QOld join Questions QNew on QOld.Question = QNew.Question and QOLD.CategoryId = @FromCategoryId and QNEW.CategoryId = @NewCategoryId join Selections S on S.QuestionId = QOld.QuestionId

Otherwise some temp tables where you populate with the Selections and Questions for a given category then push them across to the real tables might also work.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

assuming that there are no two questions the same in a category smells wrong to me
1

Assuming that QuestionID and SelectionID are IDENTITY columns you could do something simple like this:

INSERT INTO Questions (Question,CategoryID) SELECT q.Question, 2 FROM Questions q WHERE q.CategoryID = 1

which would copy all of the Questions from Category 1 to Category 2.

The problem comes with copying the Selections as you don't have any way of relating a Question to it's Selection. So you can say "get me all the Selections from all of the Questions in Category 1", but you've no way of knowing the new QuestionID's for those Questions in Category 2.

Based on the schema that you've provided, the way I would tackle this is to write a stored procedure that iterates over the Questions you want to copy in exactly the same way as your C# pseudo code. Whilst some people hate the thought of using a CURSOR in T-SQL, this is the kind of situation that it was made for. A rough stab in the dark (untested) would be something like this:

CREATE PROCEDURE PrcCopyQuestions ( @CategoryID_from NUMERIC @CategoryID_to NUMERIC ) AS DECLARE @old_QuestionID NUMERIC(10,0) @new_QuestionID NUMERIC(10,0) @Question VARCHAR(255) DECLARE c_questions CURSOR FOR SELECT QuestionID, Question FROM Questions WHERE CategoryID = @CategoryID_from FOR READ ONLY BEGIN OPEN c_questions WHILE (1=1) BEGIN FETCH c_questions INTO @old_QuestionID, @Question IF @@SQLSTATUS <> 0 BREAK INSERT INTO Questions (Question,CategoryID) SELECT @Question, @CategoryID_to SELECT @new_QuestionID = @@IDENTITY INSERT INTO Selections (Selection, QuestionID) SELECT s.Selection, @new_QuestionID FROM Selections s WHERE QuestionID = @old_QuestionID END CLOSE c_questions DEALLOCATE CURSOR c_questions END
Improve this answer

2 Comments

No no no. Never use @@identity.It will at times give the wrong answer. USe scope_Identity() instead. This can also be done without the cursor using the OUTPUT keyword.
sorry, I come from a Sybase background and there is no Scope_Identity() in Sybase as far as I am aware. Can you link to some evidence that this outputs the wrong values as it would have serious consequences if this were true. If you think my solution would benefit from changes (removing the cursor or whatever), feel free to either post your own edited version or edit mine and make it community wiki.
1

The insert/select syntax in T-SQL might give you a start:

You can select certain columns from a table, along with some hand-set values, and insert them back into the table:

Here is a simple example (probably not exactly what you want):

insert into [Questions] (Question, CategoryID) select Question, @Category2ID from [Questions] where CategoryID = @Category1ID
Improve this answer

2 Comments

how would you do this for the Selections though? You wouldn't know that the new QuestionID's are to copy them to.
This was just meant to be an example of the syntax, not a complete solution to the problem.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.