Sybase Event query (Multiple Inserts based on Select)

[gchq]

Hi there

This was the closest to Sybase I could see....

If I wanted to perform a multiple insert based on values in a table using, say a web app, I'd define a DataTable and bring in the elements using the where query, then iterate though the table using a for - next statement based on the total rows and insert new rows each time the code passed through the for - next loop...

I can build an event that will perform Inserts - but is it possible to carry out multiple inserts as in the first scenario?

Stumped!

[gchq]

I'm now part way there...

 
BEGIN
FOR names as curs CURSOR FOR SELECT Pre_Amount FROM Test_Nominal WHERE Pre_Months > 0 
DO
INSERT INTO Test_Nominal (Normal_Credit, Trans_Date) VALUES (Pre_Amount, current date);
END FOR;
end
 
 

This will now perform the multiple inserts, but it is NOT inserting the value from the select statement....

Getting closer..

[gchq]

OK - got it working now...

 
CREATE EVENT "Update_Sales_Prepayments"
SCHEDULE "Update_Sales_Prepayments_1" START TIME '07:00' ON ( 1 ) START DATE '2008-03-01'
HANDLER
BEGIN
 
FOR names as curs CURSOR FOR 
 
SELECT Sales_Pre_Amount AS vAmount, Nominal_Code as vNominal FROM Nominal WHERE Sales_Pre_Months > 0 
 
DO
 
INSERT INTO Nominal (Type, Ref, Details, Amount_Net, Ref2, Paid, Nominal_Code, Item_Date, Normal_Debit, Control_Debit, Control_Type) VALUES ('JD', 'System', 'Prepayment Adjustment', vAmount, vNominal, 'y',  '5003', current date, vAmount, vAmount, '1103');
 
INSERT INTO Nominal (Type, Ref, Details, Amount_Net, Ref2, Paid, Nominal_Code, Item_Date, Normal_Credit, Control_Credit, Control_Type) VALUES ('JC', 'System', 'Prepayment Adjustment', vAmount, '5003', 'y',  vNominal, current date, vAmount, vAmount, '1100');
 
END FOR;
 
UPDATE Nominal SET Sales_Pre_Months = (SELECT Sales_Pre_Months) - 1 WHERE Sales_Pre_Months > 0;
 
end
 
;