Reputation: 3424
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
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
Reputation: 781
for the SCOPE_IDENTITY() please refer the following URL
http://msdn.microsoft.com/en-us/library/ms190315.aspx
Upvotes: 2