SQL-SERVER Procedure How do i insert row one table then insert row into another table link to another table as foreign key

Question

For sql-server How can I insert a SKU row into table SKU_DATA, then insert the SKU into INVENTORY table with all branches and Quantity on hand=2 and Quantityonhand=0.

I need to insert a SKU item into SKUDATA and correspond row in inventory table for all existing branches Quantityonhand=2 and Quantityonhand = 0.

Please help Thanks

BRANCH

name varchar (30) not NULL,
managerNum INT NOT NULL,

SKU_DATA

SKU Int NOT NULL  IDENTITY (1000,1),
description varchar (40) NOT NULL  UNIQUE,
department varchar(30) NOT NULL default 'Home Entertainment',
sellingPrice numeric (7,2) NOT NULL ,

INVENTORY

SKU Int NOT NULL,
branch varchar (30) NOT NULL ,
quantityOnHand Int NOT NUll ,
quantityOnOrder Int NOT NUll ,

Procedure:

Create procedure InsertNewSkuWithInventory
    @description varchar (40),
    @department varchar(30),
    @sellingPrice numeric (7,2),
AS
    Declare @rowcount as int
    Declare @SKU as int
    Declare @branch as varchar(30)

    Select @rowcount = COUNT(*)
    from dbo.SKU_DATA
    Where description = @description 
      And department = @department 
      And sellingPrice = @sellingPrice; 

BEGIN
    INSERT INTO  dbo.SKU_DATA (description, department, sellingPrice)
    VALUES (@description, @department, @sellingPrice);

    Select @SKU =SKU
    From dbo.SKU_DATA
    Where description = @description 
      And department = @department 
      And sellingPrice = @sellingPrice;

    DECLARE SKUCursor CURSOR FOR
       SET @branch = @@IDENTITY

       Select SKU
       From dbo.inventory
       Where 

   CLOSE SKUCursor
   DEALLOCATE SKUCursor
END

Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. — Juan Carlos Oropeza, Sep 20 '16 at 03:55
read [Transaction] (https://msdn.microsoft.com/en-us/library/ms188929.aspx) — Denny Sutedja, Sep 20 '16 at 04:00
Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. — marc_s, Sep 20 '16 at 05:21

score 0 · Answer 1 · answered Sep 21 '16 at 08:27

There are two problems here: first, how to get the generated IDENTITY value for the newly inserted row into SKU_DATA table, and second, how to one row into INVENTORY table for each branch.

If you're inserting only a single row into SKU_DATA, then use the scope_identity() function (never use the @@IDENTITY!). Like this:

INSERT INTO  dbo.SKU_DATA (description, department, sellingPrice)
VALUES (@description, @department, @sellingPrice);
set @SKU = scope_identity()

Then you do insert into INVENTORY by selecting from BRANCH table:

insert dbo.INVENTORY (SKU, branch, quantityOnHand, quantityOnOrder)
select @SKU, b.name, 2, 0
from dbo.Branch b

Hope you can work the complete solution out from this.

SQL-SERVER Procedure How do i insert row one table then insert row into another table link to another table as foreign key

1 Answers1