How to insert values in to 2 tables, where the 2nd table need an Id from the 1st table?

Question

I have an ordering system where when a new order is placed it is inserted into my table Orders. From there I want to insert the new id into another table Importance which also needs an id from a third table called ImportanceRating.

Table structures:

Order

OrderId uniqueidentifier
TimeOrderPlaced datetime
ProductId uniqueidentifier
EstimatedDeliveryTime datetime

Importance

FK_OrderId uniqueidentifier
FK_ImpRatingId uniqueidentifier

ImportanceRating

ImpRatingId uniqueidentifier
RatingTitle varchar(50)

All of this I want merged in 1 stored procedure. How would I go about with this?

Links to good guides on the subject is more than welcome.

I'm a SPROC newbie

please show table structure and SQL statements... without that it is impossible to answer with a sample SPROC... — Yahia, Nov 19 '11 at 22:14

Nonym · Accepted Answer · 2011-11-19T22:45:57.453

Could you try this?:

CREATE PROCEDURE AddOrderAndRatingSample
    -- These are the values you want to insert
      @paramTimeOrderPlaced DATETIME
    , @paramProductId INT
    , @paramEstimatedDeliveryTime DATETIME
    , @paramRatingTitle VARCHAR(50)
AS
BEGIN

    DECLARE @siOrderId INT
    DECLARE @siImpRatingId INT

    -- Assuming that `OrderId` in table `Order` is an `identity column`:
    INSERT INTO Order (TimeOrderPlaced, ProductId, EstimatedDeliveryTime)
    VALUES(@paramTimeOrderPlaced, @paramProductId, @paramEstimatedDeliveryTime)

    SET @siOrderId = SCOPE_IDENTITY()

    -- Assuming `ImpRatingId` in table `ImportanceRating` is an `identity column`:
    INSERT INTO ImportanceRating (RatingTitle)
    VALUES(@paramRatingTitle)

    SET @siImpRatingId = SCOPE_IDENTITY()

    -- And that both `FK_OrderId` and `FK_ImpRatingId` 
            -- in table `Importance` are not `identity columns`:

    INSERT INTO Importance (FK_OrderId, FK_ImpRatingId)
    SELECT @siOrderId, @siImpRatingId

END

Updated my post. Might help you now when the tables and their columns are listed. — , Nov 19 '11 at 22:29

score 0 · Answer 2 · answered Nov 21 '11 at 17:53

Could you please try this way:

DECLARE @OrderId INT
INSERT INTO Order (TimeOrderPlaced, ProductId, EstimatedDeliveryTime)
VALUES(@paramTimeOrderPlaced, @paramProductId, @paramEstimatedDeliveryTime)

SET @OrderId = @@IDENTITY -- Last orderId

INSERT INTO ImportanceRating (RatingTitle)
VALUES(@paramRatingTitle)

INSERT INTO Importance (FK_OrderId, FK_ImpRatingId)
SELECT @OrderId, @@IDENTITY -- Here @@IDENTITY returns last ID of ImportanceRating
-- Each inserting time the global variable @@IDENTITY is set with last IDENTITY value

How to insert values in to 2 tables, where the 2nd table need an Id from the 1st table?

2 Answers2