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