How to duplicate hierarchical data

Question

I have a table containing typical hierarchical data for tasks with an arbitrary level of subtasks. ParentID is NULL for root level tasks .:

CREATE TABLE [dbo].[tblTask]( [TaskID] [int] IDENTITY(1,1) NOT NULL, [TaskDescription] [nvarchar](255) NOT NULL, [TaskStatusID] [int] NOT NULL, [ParentID] [int] NULL ) 

At the beginning of each month, I need to duplicate the hierarchy with a new TaskID for each task that is not complete (TaskStatusID == Complete), and then close all the original tasks. To save myself from grief, my first desire would be to do this in C #, with which I have more knowledge than SQL, but I would like to first try to understand if there is a good way to deal with this directly in the database.

Update: @ I'm not sure which data samples you especially need and there is no desired result. I need to duplicate the structure in the table, but with the new TaskID. This is for SQL Server.

@thursdaysgeek assumes that if the parent task is completed, all subtasks are also completed. And the rule is that if the sub-tasks of the root task are fully completed, I can set the root task. Otherwise, if the parent task is not completed, but the child task, then I only need to duplicate the parent, not the child. Hope this helps.

Answer 1

One way is to change the structure of the table and add something like a CopiedFromTaskID column, which is usually NULL.

Each month, you copy all rows for each task that is not complete, and when you insert these new rows, you also update the CopiedFromTaskID column to be the identifier of the task from which each row was copied. This allows you to bind a new line to the line from which it was copied.

 INSERT INTO tblTask (TaskDescription, TaskStatusID, ParentID, CopiedFromTaskID) SELECT TaskDescription, TaskStatusID, ParentID, TaskID FROM tblTask WHERE TaskStatusID <> Complete --Note pseudo code here 

Then you run SQL to change the ParentId of these newly inserted rows. Since you have a CopiedFromTaskID, you can use this to update the ParentID to reflect the new value, as in this SQL:

 UPDATE tblTask SET tblTask.ParentID = InlineTable.NewTaskID FROM tblTask INNER JOIN ( SELECT TaskID AS NewTaskID, CopiedFromTaskID AS OldTaskID FROM tblTask WHERE CopiedFromTaskID IS NOT NULL ) AS InlineTable ON tblTask.TaskID = InlineTable.OldTaskID WHERE tblTask.CopiedFromTaskID IS NOT NULL 

Finally, you refresh the table again to make all the CopiedFromTaskID NULL values ​​so that it is ready for the next run:

 UPDATE tblTask SET CopiedFromTaskID = NULL WHERE CopiedFromTaskID IS NOT NULL 

You want to complete all these steps inside a transaction in a stored procedure, but it does what you want, without cursors / loops and inside the database. Obviously, you would need to drop the SQL statement to "close" all the original tasks.