CODEWITHSUNDEEP
sql-serversql-server-2012
Tushar
Tushar

Reputation: 192

SQL Server : insert into two tables with case

I have two tables

  1. PriceMst
  2. ProductMst

When inserting data into ProductMst, the code inserts only one value, but in PriceMst there is a condition if price > 300 it would be insert one kg, 750 gms, 500 gms, 250 gms respective calculate there price according to weight that is 1 kg, 750 gms, 500 gms, 250 gms if price < 300 it should insert only once.

And in PriceMst generate some id which take main Productcode eg. FP-001 and generate PriceCode like this FP-001-01.

My table description are

tblProduct

   ProductCode  ProductName ProductPrize    ProductSizeID
   ------------------------------------------------------
    FP-001          ABC         200.00          4
    FP-002          PQW         500.00          3
    FP-003          ASD        1200.00          4

tblPriceMST

ProductCode ProductPriceID  ProductPrize    ProductSize  ProductUnit
----------------------------------------------------------------------
 FP-001         FP-001-05    200.00           1             KG
 FP-002         FP-002-01    500.00           1             KG
 FP-002         FP-002-02    375.00         750             GMS
 FP-002         FP-002-03    250.00         500             GMS
 FP-002         FP-002-04    125.00         250             GMS
 FP-003         FP-003-01   1200.00           1             KG
 FP-003         FP-003-02    900.00         750             GMS
 FP-003         FP-003-03    600.00         500             GMS
 FP-003         FP-003-04    300.00         250             GMS

Please guide me how to insert in two table in single query.

This query will be used in my stored procedure.

Upvotes: 0

Views: 1018

Answers (2)

GVIrish
GVIrish

Reputation: 361

You'll need to do a couple of things to make this work. For the first part of your stored procedure you'll need to insert into the tblProduct table, then store the new ProductCode in a variable. I'm assuming that ProductCode is generated via trigger or computed column. If you're not automatically generating that, the best way to do that is by creating a computed column.

Below you create your procedure with the ProductName and ProductPrice as parameters, insert them into your tblProduct, and store the new ProductCode into a @NewProductCode temp table.

CREATE PROCEDURE dbo.InsertProduct @ProductName nvarchar(255), @ProductPrice decimal(19,4)
AS
    DECLARE @NewProductCodeTempTable table (ID int)

    INSERT INTO tblProduct (ProductName, ProductPrize) 
    OUTPUT INTO INSERTED.ProductCode @NewProductCode
    VALUES (@ProductName, @ProductPrice)

Next you'll use an IF/ELSE statement to determine whether you need to insert one, or 4 records into your tblPriceMST table.

    DECLARE @NewProductCode = SELECT ID FROM @NewProductCodeTempTable

    IF (@ProductPrice < 300)   
        INSERT INTO tblPriceMST (ProductCode, ProductPriceID, ProductPrize,    ProductSize, ProductUnit)
        VALUES (@NewProductCode, @NewProductCode + '-01', @ProductPrice, 1, 'KG')
    ELSE 
    BEGIN
        INSERT INTO tblPriceMST (ProductCode, ProductPriceID, ProductPrize,    ProductSize, ProductUnit)
        VALUES (@NewProductCode, @NewProductCode + '-01', @ProductPrice, 1, 'KG')

        INSERT INTO tblPriceMST (ProductCode, ProductPriceID, ProductPrize,    ProductSize, ProductUnit)
        VALUES (@NewProductCode, @NewProductCode + '-02', @ProductPrice*.75, 750, 'GMS')   

        INSERT INTO tblPriceMST (ProductCode, ProductPriceID, ProductPrize,    ProductSize, ProductUnit)
        VALUES (@NewProductCode, @NewProductCode + '-03', @ProductPrice*.5, 500, 'GMS')  

        INSERT INTO tblPriceMST (ProductCode, ProductPriceID, ProductPrize,    ProductSize, ProductUnit)
        VALUES (@NewProductCode, @NewProductCode + '-04', @ProductPrice*.25, 250, 'GMS')            
    END
GO

I've inferred what your rules are for inserting records for the different product weights if the price is > 300. But if you have more rules, you would add additional IF/THEN or CASE statements in the ELSE block here.

There may be a much easier way to do this if you simplify your table design but without knowing all of your requirements it would be hard to say what it should look like.

Upvotes: 1

Tyron78
Tyron78

Reputation: 4197

First of all, your ProductSizeID seems to be ignored - is this the intended behaviour? It's not used in the tblPriceMST...

However, if you ALWAYS only need Prize ID 01 for unit 1 KG for all prices < 300 and always Prize ID 2 - 4 for 1 KG to 250 G for all prices >= 300, you could simply use a UNION ALL:

SELECT ProductCode, ProductCode+'-01' AS ProductPriceID, ProductPrize, 1 ProductSize, 'KG' ProductUnit
  FROM tblProduct
  WHERE ProductPrize < 300
UNION ALL
SELECT ProductCode, ProductCode+'-01' AS ProductPriceID, ProductPrize, 1 ProductSize, 'KG' ProductUnit
  FROM tblProduct
  WHERE ProductPrize >= 300
UNION ALL
SELECT ProductCode, ProductCode+'-02' AS ProductPriceID, ProductPrize*0.75, 750 ProductSize, 'G' ProductUnit
  FROM tblProduct
  WHERE ProductPrize >= 300
UNION ALL
SELECT ProductCode, ProductCode+'-03' AS ProductPriceID, ProductPrize*0.5, 500 ProductSize, 'G' ProductUnit
  FROM tblProduct
  WHERE ProductPrize >= 300
UNION ALL
SELECT ProductCode, ProductCode+'-04' AS ProductPriceID, ProductPrize*0.25, 250 ProductSize, 'G' ProductUnit
  FROM tblProduct
  WHERE ProductPrize >= 300
ORDER BY 1, 2

See SQLFiddle for details: http://sqlfiddle.com/#!18/0910e/7

Upvotes: 0

Related Questions