Help with Multiple Select in Insert statement

I have a stored procedure that copy's records from 1 set of related tables to another set of related tables.
The tables I need to copy from are layed out like...
1.  Formulas
   2.  FormulaGroups
       3.  FormulaDetails

The tables I need to copy into are layed in the same way...
1.  ClientFormulas
   2.  ClientFormulaSteps
       3.  ClientFormulaItems

The first two insert/selects work great, but I can't get the third to work.  In the third insert/select I need to get the ClientFormulaStepID's created in the 2nd insert/select and can't figure out how to do that.
I believe I probably need another select statement in order to select the clientformulastepid from ClientFormulaSteps while selecting the related details in FormulaDetails.

How would I do this?

Thanks

ALTER PROCEDURE [dbo].[stp_CopyPrimaryToClientFormulas]
	@FormulaID int,
	@CustomerID int,
	@ServiceID int,
	@RetNewFormulaID int output
	
AS


BEGIN


	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	Insert into ClientFormulas(Customerid, ServiceID, FormulaName, NatLevelID, NatToneID, DesiredLevelID, 
	DesiredToneID, PorosityID, ConditionID, GrayID, DensityID, Describe, DateCreated)
	 select @CustomerID, @ServiceID, getdate(),  NatLevelID, NatToneID, DesiredLevelID, 
	DesiredToneID, PorosityID, ConditionID, GrayID, DensityID, Describe, GetDate() from FormulasLU where 
	formulaid = @formulaid
	
	Select @RetNewFormulaID = Scope_Identity()

	Insert into clientformulasteps(clientFormulaID, StepName, FormulaTiming, AreaUsed, instructions)
	 select @RetNewFormulaID, FormulaGroupName, FormulaTiming, AreaUsed, instructions 
	FROM  FormulaProductGroup where formulaid = @formulaid


Insert into clientformulaitems(ClientFormulaID, ClientFormulaStepID, LUProdSellUseID, ProductDetailID, OuncesUsed)
 select @RetNewFormulaID, pg.FormulaGroupID, LUProdSellUseID, ProductDetailID, OuncesUsed
      FROM  formulaDetails
      INNER JOIN FormulaProductGroup ON formulaDetails.FormulaGroupID = FormulaProductGroup.FormulaGroupID
      INNER JOIN FormulasLU ON FormulaProductGroup.FormulaID = FormulasLU.FormulaID
      INNER JOIN FormulaProductGroup pg ON FormulaProductGroup.FormulaGroupName = pg.FormulaGroupName
      where  formulaDetails.FormulaID = @FormulaID  
            and pg.FormulaID = @RetNewFormulaID


	
update services set ClientFormulaID = @RetNewformulaID where serviceid = @serviceid
END

Select allOpen in new window