title: How to duplicate hierarchical data
body: <p>I have a table containing typical hierarchical data for tasks with an arbitrary level of subtasks. <em>ParentID</em> is <em>NULL</em> for root level tasks.:</p>
  
  <pre><code>CREATE TABLE [dbo].[tblTask](
      [TaskID] [int] IDENTITY(1,1) NOT NULL,
      [TaskDescription] [nvarchar](255) NOT NULL,
      [TaskStatusID] [int] NOT NULL,
      [ParentID] [int] NULL
  )
  </code></pre>
  
  <p>At the beginning of each month, I need to duplicate the hierarchy with new <em>TaskID</em>'s for each task that isn't complete (<em>TaskStatusID</em> == <em>Complete</em>), then close all the original tasks. To save myself grief my first inclination would be to tackle this in c# with which I have greater proficiency than SQL but I would like to first try to understand if there is a good way to tackle this directly in the database.</p>
  
  <p>Update: @Abe I'm not sure what sample data you need particularly, and there is no desired output. I need to duplicate the structure in the table but with new <em>TaskID</em>'s. This is for SQL Server.</p>
  
  <p>@thursdaysgeek assume that if a parent task is complete then all sub-tasks are also complete. And the rule is that if the sub-tasks of a root task are all complete then I can set the root task to complete. Otherwise if a parent task is not complete but the child task is then I need to only duplicate the parent and not the child. Hope that helps.</p>