Select multiple records into a table

[Dave]

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),Custom erRates.custid,
rates.Price, rates.name
FROM CustomerRates INNER JOIN Rates ON CustomerRates.R ateid = 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.

[Erland Sommarskog]

Re: Select multiple records into a table

Dave (funkdm1@yahoo. com) writes:[color=blue]
> When using the conventional method, I get something alongthe lines of
> " insert failed. more than one record was returned"[/color]

It is always adviceable to post the exact error message you get.
[color=blue]
> -------
> INSERT INTO detail
> SELECT (SELECT max([id])FROM iheader),Custom erRates.custid,
> rates.Price, rates.name
> FROM CustomerRates INNER JOIN Rates ON CustomerRates.R ateid = rates.ID
> WHERE NextBill > GETDATE()
> -------[/color]

I can't see anything wrong with this. I would guess that the message
comes from a trigger. If you run the INSERT statement in Query Analyzer,
pay attention to the error number and the procedure. If the error number
is >= 50000 it's a user-defined error.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp