How can I copy data using T-SQL?

Question

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?

Answer 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

Answer 2

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

Answer 3

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.