Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Server 2000 query

Posted on 2014-01-15
3
Medium Priority
?
270 Views
Last Modified: 2014-01-16
First, I must use a SQL Server 2000 database.  

I have a dataset that looks like this:

supplierid      deliverydate      familyid      productid      QtyOrdered
8010382      2014-01-20 00:00:00.000            30898      40496      2
8010382      2014-01-20 00:00:00.000            30908      40506      4
8010382      2014-01-20 00:00:00.000            30909      40507      2
8010382      2014-01-20 00:00:00.000            30910      40508      3
8010382      2014-01-20 00:00:00.000            30913      40511      2
8010382      2014-01-20 00:00:00.000            31513      41126      2
8010382      2014-01-20 00:00:00.000            31514      41127      2
8010382      2014-01-20 00:00:00.000            31557      41171      2
8010382      2014-01-20 00:00:00.000            32119      41775      5
8010476      2014-01-19 00:00:00.000            30993      40591      1
8010453      2014-01-19 00:00:00.000            31034      40632      1
8010453      2014-01-19 00:00:00.000            31035      40633      1
8010453      2014-01-19 00:00:00.000            31045      40643      1

I need to write a file for each distinct supplierid.  I cannot use a cursor.  

For example I need 3 text files..one for 8010382, 8010476 and 8010453.    I need to read all the records for 8010382 and write to a file, then do the same for the other two.

How can I loop through this table without using a cursor.  I would normally use a CTE, but I cannot because of the version of SQl I must use.

Anybody have any ideas.

Thanks.
0
Comment
Question by:sherbug1015
  • 2
3 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 2000 total points
ID: 39783696
ok this what you can do

declare @MinID BIGINT
DECLARE @maxID BINGT

SELECT @minID = MIN(supplierID) FRom <yourTable>
SELECT @MaxID = mAX(supplierID) from <your table>
WHILE @minID <= @maxID
BEGIN

-- Write to File using @minID

SELECT TOP 1 @minID = SupplierID 
FROM <your Table>
where supplierID > @minID
ORDER BY supplierID 


END

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39783898
how do you actually "plan" to "write" to a file?
0
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39783980
@Hengel, if the question is for me, I left that part to user, as he might already have the command or else a I could have filled it up with a simple BCP out.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question