Adding multiple lines to a table using the identity from another table

Currntly I am using the following code to enter details of the customer into the customers table and details of their order into the orders table.  Is it possib;e to add multiple rows into the orders table where one customer requres more than one product
ALTER PROCEDURE [dbo].[usp_AddOrder] 

	@CompName		VARCHAR(50),
	@CustNo			VARCHAR(50),
	@LeadSource		VARCHAR(50),
	@Staff			VARCHAR(25),
	@ContactName	VARCHAR(50),
	@ContactTelNo	VARCHAR(20),
	@ProdID			VARCHAR(25),
	@ProdQty		FLOAT,
	@Discount		FLOAT
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.


INSERT INTO Company(CompName, CustNo, LeadSource, Staff, ContactName, ContactTelNo)
VALUES  (@CompName, @CustNo, @LeadSource, @Staff, @ContactName,@ContactTelNo)

SELECT @CompanyID=@@Identity

INSERT INTO Orders(CompanyID, ProdID, ProdQty, Discount, OrdDate)
VALUES(@CompanyID, @ProdID, @ProdQty, @Discount, GETDATE())

Open in new window

If I use the @@Identity again, I think it will pick up this from the recently added line in the orders tabl

Any help would be appreciated
Who is Participating?
dqmqConnect With a Mentor Commented:
For the first order, use Scope_Identity() instead of @@Identity.

For the second order, use @CompanyID which was saved before inserting the first order.

Now, let's get real....

You really need to pull product out of the Orders table:

Order (OrderNo, CompanyID, OrderDate)
OrderLine (OrderNo, LineNo, ProdID, ProdQty, Discount

Also, you need to think about your procedure design.  You can't add a second order for the same customer.  Even if you change it to add a second product to the same order, then you need to pass two sets of product info in the parameters. What then, if you want to add a third product to the order?

I suggest creating three independent procs:

If desired, add a fourth proc to orchestrate the first three.


ZbiebuAuthor Commented:
I have a go at that.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.