user478636
user478636

Reputation: 3424

Order and OrderDetails

If i am going to save a record of OrderDetails, I need the OrderId(foreign key) from the Order table. The question is when saving a record of Order, the OrderId is automatically generated

So how do I refer to that particular OrderId, when inserting a record in OrderDetails? This has confused me so much.

I am using Microsoft SQL Server 2005

Order Table

OrderId
CustomerId
Date
Total

OrderDetails

OrderDetailId
OrderId
ProductId
Quantity
UnitPrice

Upvotes: 0

Views: 2180

Answers (2)

benwong
benwong

Reputation: 2226

The answer will depend on what datatype your OrderID is. Assuming it's an int identity, you can call SCOPE_IDENTITY() to return the identity of the last added row.

For example

INSERT INTO dbo.Orders (CustomerId, Date, Total)
VALUES (@CustomerId, @Date, @Total);

DECLARE @OrderID int
SET @OrderID = SCOPE_IDENTITY()

Upvotes: 2

Related Questions