How do i do multiple insert into multiple table in sql server

Question

Database diagram

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

Trigger

 CREATE TRIGGER NewSku
ON dbo.SKU_DATA
After INSERT
as
   begin
        SET NOCOUNT ON;

  declare
@description varchar (40),
    @department varchar(30),
    @sellingPrice numeric (7,2)


    DECLARE @Output TABLE (SKU INT) 

    INSERT INTO  dbo.SKU_DATA ([DESCRIPTION], DEPARTMENT, SELLINGPRICE)
        OUTPUT INSERTED.SKU INTO @Output(SKU)--getting Inserted ID from SKU_DATA to @Output table
    VALUES (@description, @department, @sellingPrice);


    INSERT INTO INVENTORY (SKU ,branch ,quantityOnHand ,quantityOnOrder ) 
             --Loading SKU information against all branches to INVENTORY
    SELECT t.SKU,b.name,2, 0
    FROM Branch b
    CROSS APPLY (SELECT SKU FROM @Output)t
END

NewSku, Line 17 Cannot insert the value NULL into column 'description', table 'tt.dbo.SKU_DATA'; column does not allow nulls. INSERT fails.

Unnikrishnan R · Accepted Answer · 2016-09-26 00:55:31Z

2

In sql, you can get the inserted row details from INSERTED tables.

CREATE PROCEDURE InsertNewSkuWithInventory
        @description varchar (40),
        @department varchar(30),
        @sellingPrice numeric (7,2)
    AS


    BEGIN

        DECLARE @Output TABLE (SKU INT) 

        INSERT INTO  dbo.SKU_DATA ([DESCRIPTION], DEPARTMENT, SELLINGPRICE)
            OUTPUT INSERTED.SKU INTO @Output(SKU)--getting Inserted ID from SKU_DATA to @Output table
        VALUES (@description, @department, @sellingPrice);


        INSERT INTO INVENTORY (SKU ,branch ,quantityOnHand ,quantityOnOrder ) 
                 --Loading SKU information against all branches to INVENTORY
        SELECT t.SKU,b.name,2, 0
        FROM Branch b
        CROSS APPLY (SELECT SKU FROM @Output)t
    END

If you wanted to write a trigger , remove the inventory insert part from the above procedure and write a trigger like below. For every insert in your SKU_Data table this trigger will get fired and will insert data to the Inventory table.

Procedure :

  CREATE PROCEDURE InsertNewSkuWithInventory
        @description varchar (40),
        @department varchar(30),
        @sellingPrice numeric (7,2)
    AS


    BEGIN 

        INSERT INTO  dbo.SKU_DATA ([DESCRIPTION], DEPARTMENT, SELLINGPRICE)
        VALUES (@description, @department, @sellingPrice);
END

Trigger:

  CREATE TRIGGER UpdateInventory
   ON dbo.SKU_DATA
   AFTER INSERT
  AS

  INSERT INTO INVENTORY (SKU ,branch ,quantityOnHand ,quantityOnOrder ) 
         --Loading SKU information against all branches to INVENTORY
 SELECT t.SKU,b.name,2, 0
 FROM Branch b
 CROSS APPLY (SELECT SKU FROM INSERTED)t

edited Sep 26, 2016 at 0:55

answered Sep 21, 2016 at 6:14

Unnikrishnan R

5,0511 gold badge15 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Unnikrishnan R Over a year ago

@Tanjim Rahman : as per Zack ,he wanted to insert data into SKU_Data table and the inserted ID from SKU_Data table is again inserted to Inventory table against all branches. So what is the problem in the above query?

Zack Sng Over a year ago

As i am new to this, i do not know how to write the syntax for some of these code needed

Unnikrishnan R Over a year ago

@Tanjim Rahman : I am not getting what you are saying. Output will return value only when an insert made to the SKU_DATA and Inserted ID is saving to the output table.This is the usual way following in SQL to insert data in to multiple table from the output of another table.

Unnikrishnan R Over a year ago

@Zack : Tell me where you have doubt in syntax?

Zack Sng Over a year ago

Hi , Unnikrishnan R Great thank you so much... it works. i am really new to stored procedure and trigger in SQL database that why i have problems. Have a nise day.

|

Esty · Accepted Answer · 2016-09-21 05:51:34Z

You don't need a cursor. Just insert all branches to #BranchList and rest is as following.

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

        CREATE TABLE #BranchList
        (
            branch varchar(30)
        )

    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;

        INSERT INTO #BranchList
        -- Your code to fetch all branches

        INSERT INTO INVENTORY
        SELECT @SKU, branch, 2, 0
        FROM #BranchList
    END

Collectives™ on Stack Overflow

How do i do multiple insert into multiple table in sql server

2 Answers 2

13 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related