# Having problem with insert

I am a fresh grad and i have been assigned with the following task which i feel is really tough for my level

table a
item 01 02 03 04 05 06 rsp
ListId
Unit price

table b
listid 01 02 03 04 05 06 RSP
factor 0.2 0.5 2 1 3 0.2 0
baselistid RSP RSP 01 02 03 03 NULL

When i generate RSP item with unit price of \$ 2 it will then generate 2 item which is 01(\$0.4) and 02(\$1),
then it is suppose to auto generate 03(01, \$0.8) and 04(02, \$1) which in the ends generate 05(03,\$2.4 ) and 06(03, \$0.16)

result for table a when create item with list id rsp
item a a a a a a
ListId 01 02 03 04 05 06
Unit price 0.4 1 0.8 1 2.4 0.16

• @Item and @PriceListId are sent in as parameter, In this case @Item = 'a' @priceListId = 'rsp'
i am able to generate only the first level using
INSERT INTO [tablea] (item, listid, unitprice)
SELECT @Item, [P].[ListId], @CurrencyUnitPrice *( ISNULL([P].[Factor],0))
FROM [tableb] [P] LEFT OUTER JOIN [tableb] [PL] ON [PL].[ListId] = [P].[BaseListId]
where [P].[BaseListId] = @PriceListId AND [P].[BaseListId] IS NOT NULL AND [P].[Active] = 1

**assuming the faact that table b will not create loop data

I really hope if someone would be able to help me

Solution Found

CREATE TABLE #testing(testingid int, NewCurrencyUnitPrice decimal(19, 5))
INSERT INTO #testing(testingid, NewCurrencyUnitPrice)
VALUES(@ListId, @UnitPrice)

WHILE ( EXISTS (SELECT * FROM #testing)
BEGIN

UPDATE TABLEA .....

``````INSERT INTO #testing ([testingid],[NewCurrencyUnitPrice])
``````

SELECT ListId, UnitPrice * Factor FROM TABLEA LEFTOUTER JOIN TABLEB
END

If you could post DDL for your tables and insert statements to populate the tales you're selecting from perhaps you will get some useful replies.

Hi Jotorre , I kinda solve it by using temporary table where each generated item i put the new currency and the listid inside then reloop till the temporary table is empty but i am not sure if my boss would accept the answer. awaiting his arrival to ask him.

Never the less thank you for taking your time to look at it

If you have a possible solution, perhaps you could post it for the benefit of others.

sure.i edited a summary of the solution up in the post