copying related data using CTE within one SP?

Question

I have to copy data for one student (clone student) that encompass three tables:

1. tblStudent (where PK is studentId)
2. tblStudentSubjects (where PK is StudentsubjectId and FK is StudentId)
3. tblStudentSubjectComments (where PK doesn't exist and FK is StudentSubjectId)

To accomplish this I would have to

1. Create Student record by coping existing student record and obtain new studentid
2. create student subject record for each existing student subject using new studentid and obtain new StudentSubjectId
3. create student subject comment record for each existing student subject comment using new studentSubjectId

... hope you got idea

So, is there a elegant way to accomplish this using CTE in one Store Procedure? (I'm using SQL Server 2005)

Answer 1

Have you tried using OUTPUT INTO to get the results from the initial operations into another table and repeat?

Answer 2

INSERT and UPDATE statements only operate on a single table at a time. You have 3 tables that need INSERTs. You can definitely INSERT multiple rows into the table in a single shot, and I think that's good to do whenever possible, but you can't put rows in multiple tables simultaneously.

Think of it this way. When you query the data you are pulling records from many sources into a single result set. When you modify the data, you are pulling records from many sources to apply changes to a single result set.

Also, I hate to say it but there are some things that don't require a super-elegant solution. Your stored procedure just needs to apply some best practices to ensure all 3 INSERT statements happen as an atomic transaction (all or nothing). The end result may be verbose and uninteresting, but it will be quick, effective, safe, understandable, and maintainable.