CODEWITHSUNDEEP
sql-serverstored-procedures
AliAzra
AliAzra

Reputation: 919

Stored procedure => update table with new data

I have two tables, Price List and Price Code:

CREATE TABLE PriceLists
(
    PriceListID INT IDENTITY(1,1) NOT NULL,
    Reference NVARCHAR(50) NOT NULL,
    Description NVARCHAR(255) NOT NULL
)

Each Price List had lots of price codes:

CREATE TABLE PriceCodes
(
    PriceCodeID INT IDENTITY(1,1) NOT NULL,
    PriceListID INT NOT NULL
    PriceCodeName NVARCHAR(50) NOT NULL
)

Basically what I need is a stored procedure; when a new price list is entered, that stored procedure is going to add same number of price codes to the PriceCodes table with new PriceListID.

For example:

If a new row is inserted into the PriceList table with PriceListID = 1 and Reference = 2017, then the stored procedure should insert these rows into the PriceCode table:

PriceCodeID    PriceListID   PriceCodeName
------------------------------------------
    1               1        CodeA 
    2               1        CodeB 
    3               1        CodeC

And if we have this data in the PriceList table :

PriceListID    Reference
------------------------
      1          2017 
      1          2018

then the PriceCodse table should be like this:

PriceCodeID    PriceListID   PriceCodeName
------------------------------------------
    1               1        CodeA 
    2               1        CodeB 
    3               1        CodeC
    4               2        CodeA 
    5               2        CodeB 
    6               2        CodeC

Upvotes: 1

Views: 551

Answers (2)

marc_s
marc_s

Reputation: 754268

You could easily package this up into an AFTER INSERT trigger, and even get rid of that ugly cursor - try this:

CREATE TRIGGER trg_InsertPriceCodes
ON dbo.PriceLists
AFTER INSERT
AS
BEGIN
    INSERT INTO dbo.PriceCodes (PriceListID, PriceCodeName)
        SELECT i.PriceListID, VAL.Name
        FROM Inserted i
        CROSS APPLY (VALUES ('CodeA'), ('CodeB'), ('CodeC')) AS VAL(Name) 
END

So now, any time you insert a row into PriceLists, the trigger will take care of adding three rows with values CodeA, CodeB, and CodeC for PriceCodeName into the PriceCodes table. No stored procedure and no RBAR (row-by-agonizing-row) trigger necessary.....

Upvotes: 1

AliAzra
AliAzra

Reputation: 919

I wrote this code and it seems working, just need few adjustments. 

SET NOCOUNT ON;
DECLARE @priceCodeName VARCHAR(50), @PriceListID INT, @PriceGroup VARCHAR(20);
DECLARE priceCode_cursor CURSOR FOR 
SELECT  PriceCodeName ,PriceListID ,PriceGroup
FROM PriceCodes2
ORDER BY PriceCodeName;

OPEN priceCode_cursor

FETCH NEXT FROM priceCode_cursor
INTO @priceCodeName, @PriceListID, @PriceGroup

WHILE @@FETCH_STATUS = 0
BEGIN

    INSERT INTO [dbo].[PriceCodes2]
           ([PriceCodeName]
           ,[PriceListID]
           ,[PriceGroup])
     VALUES
           (@priceCodeName,@PriceListID,@PriceGroup)

    FETCH NEXT FROM priceCode_cursor
    INTO @priceCodeName, @PriceListID, @PriceGroup

END
CLOSE priceCode_cursor;
DEALLOCATE  priceCode_cursor;

Upvotes: 0

Related Questions