Question on varying filenames in loops

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Question on varying filenames in loops

Author

Topic

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-08-01 : 09:56:46

I am using QA to export bits of a table I have in discrete parts, using a unique identifier column called 'rowid'.

I have a simple loop as follows:

declare @min int
declare @max int
declare @next int

select @min = 1
select @max = count(*) from mytable

while @min <= @max
begin
select @next = @min +49999
select * from mytable where rowid >= @min and rowid <= @next
order by rowid
select @min = @min+50000
end

which does the trick in increments of 50k rows just fine. However, how would I incorporate a varying filename, as I'm using the 'Results to File' option under the Query menu to export this data to a text file. As it is, the loop just runs and resaves under the first filename I give.

(Note: I realize there are infinitely better ways to do this, but I'm having to work with a legacy system here which requires processing of text data in such increments and I have no other way of controlling the load into the other application.)

Any ideas would be much appreciated.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-01 : 10:13:11

you could use a .bat file that runs an osql command:
osql /U username /P password /d dbname /S Server /Q "YourQueryHere" -o fileName.txt

Go with the flow & have fun! Else fight the flow

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-08-01 : 10:28:59

Hi,

Thanks for your response. However, I'm not sure how I would incorporate a loop to change the filename each time (say 'x1' with 1 incrementing each time to indicate another file).

Any ideas?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-01 : 11:55:55

read this:
http://weblogs.sqlteam.com/robv/articles/4102.aspx

it may give you some ideas