DECLARE @cnt INT
set @cnt = 0
WHILE @cnt <= (select count(prodref) from dba.proditem where prodref like '840%')
BEGIN
INSERT INTO dba.proditemlinks
(opco_code,prodref,linked_prodref, defaultquantity, ratioquantity, linktype, triggercascade, removeparentitem, startdate, enddate,sequence, invisible, serviceitem, cataloguecode)
select opco_code,(Select top 1 t1.prodref from dba.proditem as t1 LEFT JOIN dba.proditemlinks as t2 ON t1.prodref = t2.prodref where t1.prodref like '840%' and t2.prodref is null),linked_prodref, defaultquantity, ratioquantity, linktype, triggercascade, removeparentitem, startdate, enddate,sequence, invisible, serviceitem, cataloguecode from dba.proditemlinks where prodref = '840'
set @cnt = @cnt+1
End
Which appears to work well (it may not be the best option, but with my limited know how and a little help from these forums its working) until I think it gets to the end and gives me an error that dba.proditemlinks.prodref cannot be null.
Can anyone help me see the error that I am clearly missing!
That will re-Count every time around the loop, not sure that is the best way. If you just need to know the number of rows then I would do the COUNT first (into a @TempVariable) and use that in your WHILE loop.
If what you actually want? is to process each matched row inside your loop (which you aren't currently doing, I presume your loop is reselecting the same row each tmie) then I would do the following:
Get the PKey for all matching rows into a #TEMP table together with an IDENTITY value (SORT the values, IF you want them processed in a specific order inside your LOOP)
SELECT IDENTITY(int, 1, 1) AS T_ID,
PKey1, PKey2, ...
INTO #TempTableName
from dba.proditem
where prodref like '840%'
-- optional ORDER BY ...
SELECT @TotalCnt = @@ROWCOUNT -- Remember the number of rows added to #TempTableName
then for your loop
set @LoopRow = 1
WHILE @LoopRow <= @TotalCnt
BEGIN
...
SELECT @LoopRow = @LoopRow + 1
END
Inside the loop, you can get the actual row from [proditem] using a JOIN:
INSERT INTO ...
SELECT ...
FROM #TempTableName AS T
JOIN dba.proditemlinks AS PIL
ON PIL.PKey1 = T.PKey1
AND PIL.PKey2 = T.PKey2
...
WHERE T.T_ID = @LoopRow -- Only matches the "current" row for the LOOP
But if all you want to do is to INSERT some new rows into [proditemlinks] for every occurrence
FROM dbo.proditem
WHERE prodref like '840%'
then you ought to be able to do something like:
INSERT INTO dba.proditemlinks
(opco_code,...)
SELECT opco_code, RelevantColumn(s)From[proditem], ...
FROM dba.proditem
WHERE prodref like '840%'
We create new products throughout the day into dba.proditem some of the items created need to be added to the dba.proditemlinks table, and I have to create 43 lines against each new product that is created into this table. The idea is in our system if you buy 'X' you might also want Y,Z,etc.
So I created the insert statement which copies the 43 lines from an existing product in dba.proditemlinks and creates them with the new part number.
So to automate this I created the Loop. My idea was I would count how many new products there were, the loop through them, only selecting the top 1 item each time, and run the insert command.
It would be better to do that in a single INSERT statement (that inserted all 43 rows as one batch).
If you can SELECT the relevant 43 rows (from Product table, maybe?), then that would form the basis of the INSERT statement.
Isn't your "if you buy 'X' you might also want Y,Z,etc." based on sales? i.e. for a new product that will be no suitable data?
Personally I would re-fresh the 43 items (I presume that is the number of "Similar / companion products" that displays on your front end?) each night, based on actual sales
In our eCommerce stuff we have "hints" and all sorts for this ...
When the product is set up the operator can choose companion products - batteries and accessories etc.
An operator can set up push-products - slow moving items with high inventory stock numbers
If we get a TV mention we may want to raise the visibility of that product (if we have plenty of stock), or (if minimal stock) we avoid showing it on any companion list (unless it is explicitly marked as a companion product), so that we will retain stock for people who specifically look for that product, but not attempt to sell it as an up-sale.
if all you want to do is to INSERT some new rows into [proditemlinks] for every occurrence