I am building an invoicing database. I have no problems searching for
due dates and generating the invoice header. The problem is generating
the invoice detail.
My customers may have more than one item that needs to go into the
invoice detail table.
For example:
customer #123 has 2 items that need to be placed into the detail
table.
Rate 1 email account
Rate 2 hosting account
I have to get both of these records into the detail table.
When using the conventional method, I get something alongthe lines of
" insert failed. more than one record was returned"
-------
INSERT INTO detail
SELECT (SELECT max([id])FROM iheader),CustomerRates.custid,
rates.Price, rates.name
FROM CustomerRates INNER JOIN Rates ON CustomerRates.Rateid = rates.ID
WHERE NextBill > GETDATE()
-------
I have even considered a cursor to loop through the records but I cant
make it run properly. I am not crazy about the performance of cursors
anyway.
Any aideas would be greatly apreciated.