How to insert values ​​in 2 tables, where the 2nd table requires an ID of the first table?

advertisements

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


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