T-SQL: Best way to copy hierarchy data?

Question

My database looks like this:

Questionnaire 
Id 
Description

Category
id
description
QuestionnaireId (FK)    

Question
id
CategoryId (FK)
field

When I copy a questionnaire, I'd like to copy all the underlying tables. So this means that the table Questionnaire gets a new Id. Then, all the belonging categories of the questionnaire must also be copied. So the newly inserted categories must get the new questionnaire Id. After the categories, the questions must be copied. But the categoryId must be updated to the newly inserted category.

How can I do this using t-sql?

Answer 1

This is pretty easy to accomplish, but you have to keep track of everything as you go. I would generally create a single SP for this, which takes as an input the questionnaire to copy.

  DECLARE @newQuestionnaireId INT
  INSERT INTO Questionnaire
  (Id,Description)
  SELECT Id, Description 
  FROM Questionnaire
  WHERE ID = @sourceQuestionnaireID
  SET @newquestionnaireId = SCOPE_IDENTITY()

At this point you have a new header record, and the newly generated Id for the copy. The next step is to load the categories into a temp table which has an extra field for the new Id

DECLARE @tempCategories TABLE (id INT, description VARCHAR(50),newId INT)
INSERT INTO @tempCategories(id,description)
SELECT id, description FROM Category 
WHERE questionnaireId = @sourceQuestionnaireId

Now, you have a temp table with all the categories to insert, along with a field to backfill the new ID for this category. Use a cursor to go over the list inserting the new record, and use a similar SCOPE_IDENTITY call to backfill the new Id.

DECLARE cuCategory CURSOR FOR SELECT Id, Description FROM @tempCategories
DECLARE @catId INT, @catDescription, @newCatId INT
OPEN cuCategory
FETCH NEXT FROM cuCategory INTO @catId,@catDescription
WHILE @@FETCH_STATUS<>0
BEGIN
  INSERT INTO Category(description,questionnaireId)
  VALUES(@catDescription,@newQuestionnaireId)
  SET @newCatId = SCOPE_IDENTITY()

  UPDATE @tempCategories SET newCatId=@newCatId
  WHERE id=@catId
  FETCH NEXT FROM cuCategory INTO @catId,@catDescription
END
CLOSE cuCategory
DEALLOCATE cuCategory

At this point you now have a temp table which maps the catId from the original questionnaire to the catId for the new questionnaire. This can be used to fill the final table in much the same way - which i'll leave as an excercise for you, but feel free to post back here if you have difficulty.

Finally, I would suggest that this whole operation is carried out within a transaction to save you from half completed copies when something goes wrong.

A couple of disclaimers: The above was all typed quickly, dont expect it to work off the bat. Second, Ive assumed that all your PK's are identity fields, which they should be! If they're not just replace the SCOPE_IDENTITY() calls with the appropriate logic to generate the next ID.

Edit: documentation for Cursor operations can be foundhere

Answer 2

I had a problem like this and began to implement the solution suggested by @Jamiec but I quickly realised that I needed a better solution because my model is much larger than that in the example cited here. I have one master table with three intermediate tables, each of which have one or more tertiary tables. And the three intermediates each had something like 50 columns. This would mean a lot of work to type all that up, particularly in the fetch part with the temporary memvars. I tried to find a way to FETCH directly into the temp table but it seems you cannot do that. What I did was add a column to the intermediate tables called OriginalId. Here is my code translated into the model used by the asker:

DECLARE @newQuestionnaireId INT
INSERT INTO Questionnaire (Id,Description)
SELECT Id, Description FROM Questionnaire
WHERE ID = @sourceQuestionnaireID
SET @newquestionnaireId = SCOPE_IDENTITY()

INSERT INTO Category(QuestionnaireId, description, originalId)
SELECT @newquestionnaireId, description, id FROM Category 
WHERE questionnaireId = @sourceQuestionnaireId

INSERT INTO Question SELECT Category.Id, Question.Field
FROM Question join Category on Question.CategoryId = Category.OriginalId
WHERE Category.QuestionnaireId = @newquestionnaireId

In my model the id fields are all Identities so you do not supply them in the inserts.

Another thing I discovered before I gave up on the CURSOR approach was this clever little trick to avoid having to type the FETCH statement twice by using an infinite WHILE loop with a BREAK: