Zack Sng Zack Sng - 2 months ago 9
SQL Question

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

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.

Answer

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