title: TSQL Parent > Child > Sub-Child duplication without cursor
body: <p>I am creating a SQL 2008 R2 stored procedure to duplicate a row and all it's children.</p>
  
  <p>It's a 3-tiered setup with a Parent, Child and Sub-Child
  Given the ID of the parent I need to create a duplicate.</p>
  
  <p>I have solved it using a <code>fast_forward</code> <code>cursor</code>. </p>
  
  <p>I know I can also do it with a while loop through rows but I do not believe that will be faster than this cursor method. What are your thoughts?</p>
  
  <p><strong>Is there a better way to accomplish this task without using cursors?</strong></p>
  
  <p>EDIT: Another option I considered was creating a temp table holding the old / new PKID's of the TBLACStages records.</p>
  
  <p>TBLACStages may have anywhere from 1 to 20 corresponding rows (and TBLACUpgrade will likely have 3 rows per TBLACStages row)</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[spDuplicateACUnit]
  @pACUnitID bigint = 0 
  AS BEGIN
  SET NOCOUNT ON;
  
  DECLARE @NewACUnitID bigint = 0
  
  INSERT INTO TBLACUnits ([col1] ,[col2] ,[...] ,[coln]) SELECT [col1] ,[col2] ,[...] ,[coln] FROM TBLACUnits WHERE ACUnitID = @pACUnitID
  
  SELECT @NewACUnitID = SCOPE_IDENTITY()
  
  DECLARE @ACStageID bigint = 0 
      DECLARE @NewACStageID bigint = 0
  
  DECLARE @ACUnitCursor CURSOR
  
  SET @ACUnitCursor = CURSOR LOCAL FAST_FORWARD FOR SELECT ACStageID FROM TBLACStages WHERE TBLACStages.ACUnitID = @pACUnitID
  
  OPEN @ACUnitCursor
  
  FETCH NEXT FROM @ACUnitCursor INTO @ACStageID
  
  WHILE @@FETCH_STATUS = 0 
  BEGIN
  
  INSERT INTO TBLACStages ([ACUnitID] ,[col1] ,[col2] ,[...] ,[coln]) SELECT @NewACUnitID ,[col1] ,[col2] ,[...] ,[coln] FROM TBLACStages WHERE TBLACStages.ACStageID = @ACStageID
  
  SELECT @NewACStageID = SCOPE_IDENTITY()
  
  INSERT INTO TBLACUpgrade ([ACStageID] ,[col1] ,[col2] ,[...] ,[coln]) SELECT @NewACStageID ,[col1] ,[col2] ,[...] ,[coln] FROM TBLACUpgrade WHERE TBLACUpgrade.[ACStageID] = @ACStageID
  
  FETCH NEXT FROM @ACUnitCursor INTO @ACStageID 
  END
  
  CLOSE @ACUnitCursor DEALLOCATE @ACUnitCursor
  
  END
  
  GO
  </code></pre>