troubleshooting Question

Help with Multiple Select in Insert statement

Avatar of Sheritlw
SheritlwFlag for United States of America asked on
Microsoft SQL Server 2005
5 Comments1 Solution347 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 5 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros