asked on

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

Open in new window

wdosanjos

You need to use the @@IDENTITY system variable. It provides the identity last inserted. Something like this:

declare @clientformulastepsId long

-- your code

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

set @clientformulastepsId = @@IDENTITY

-- remaining code

Open in new window

I hope this helps.

Sheritlw

ASKER

The 2nd insert, inserts multiple records so there will be multiple clientformulastepids.
I need to be able to match up the related information while capturing the newly created clientformulastepids.

Thanks

Lowfatspread

don't use @@Identity it doesn't return what you want Always use scope_identity()

ASKER CERTIFIED SOLUTION

Sheritlw

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Sheritlw

ASKER

Figured it out and posted solution.