title: Multiple Table Insert with Merge?
body: <p>I am trying to insert some rows in a parent/child relationship. How does one accomplish this in SQL?</p>
  <p>This would be the base query I'd use to find the info I would be inserting:</p>
  <pre><code>SELECT * FROM parentTable p
  INNER JOIN childTable c
      ON p.ID = c.ParentTableID
  WHERE p.PlanID = 123456
  </code></pre>
  <p>What needs to happen is that I insert the ParentTable row first which is really just a copy of the matched row but with a new PlanID and Created Date.  Then I take that ParentTable ID from the inserted row and use that for the same process but for the Child Table.</p>
  <p>So I need to do in .Net speak at least is where I loop through all parentTable matches and for each match create 1 childTable row.</p>
  <p>I was trying to use <code>MERGE</code> and the <code>OUTPUT</code> clause but it seems like I'm maybe using a square peg for a round hole.  Would I be better off using a CURSOR of some sorts?</p>
  <hr />
  <p>So this is what I have so far based on the answer below.  Unfortunately it isn't grabbing the <code>SCOPE_IDENTITY()</code>...is there a trick to it?  Because that is returning NULL I get FK errors on the inserts.</p>
  <pre><code>USE MemberCenteredPlan
  
  DECLARE @NewPlanID BIGINT;--49727690
  DECLARE @OldPlanID BIGINT;--49725211
  DECLARE @NewSWReAssID BIGINT;
  
  SET @NewPlanID = 49727690
  SET @OldPlanID = 49725211
  
  BEGIN
  INSERT INTO tblSocialWorkerReAssessment 
  SELECT 
      @NewPlanID
      ,[Discussed]
      ,Discussion
      ,NULL
      ,NULL
      ,NULL   
      ,CreatedBy
      ,GETDATE()
      ,NULL
      ,NULL
      FROM tblSocialWorkerReAssessment
      WHERE PlanID = @OldPlanID
  
  SELECT @NewSWReAssID = SCOPE_IDENTITY();
  
  INSERT INTO tblPlanDomain
  SELECT 
     @NewPlanID
     ,[DomainTypeID]
     ,[MemberOutcomes]
     ,[MemberPreferences]
     ,[DomainDate]
     ,[Steps]
      ,[ClinicalFunctionalConcerns]
      ,[ReportWageES]
      ,[ReportWageSSA]
      ,@NewSWReAssID
      ,[CreatedBy]
     ,GETDATE()
     ,NULL
     ,NULL   
     ,NEWID()
  FROM tblPlanDomain 
  WHERE planID = @OldPlanID
  
  END
  </code></pre>