Avoiding loop to get a resultset

Let me explain the scenario (SQL 2000 DTS):
I've a order table and order_detail table.
Order_detail table refers Order table for order_id. Since one order can have multiple items within it we've the order_detail table. So Order->order_detail is a one->many relationship.
Now I want to export the data into a flat file. the format is something like this:

order1_id, order number, date
Order_detail_id2, item_name2
order2_id, order number, date
order_detail_id4, item_name4

So I need to write one line from the order table and then follows the corresponding order_detail table records. Again need to write the order table record and so on. Is there way to do this other looping? I knw we can use a while loop or cursor, I want to know if therz a better way of writing this in t-SQL only avoiding loop.

Who is Participating?
Ved Prakash AgrawalConnect With a Mentor Database Consultant/Performance ArchitectCommented:
can you used vbscript in you dts package and in which you write you loops.
i don't think so without looping we can achive the same things.
LowfatspreadConnect With a Mentor Commented:
you could write a select statement to do it

but a loop is probably best....

(you may also wish to experiment with an xml output?)


Select case Linetype when  0 then convert(char(10),o.orderno)+'...'
                   else convert(....

from Order as O
Inner join OrderLine as OL
 on o.orderno=ol.orderno
cross join (select 0 as linetype union select 1) as L
Where (ol.sequence = 1 and l.linetype=0)
   or (l.linetype=1)
rsrsmAuthor Commented:
Thank you both since both the answers were quick and almost similar I've divided the points. Please let me know if there are any concerns.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.