StartRow - Endrow sth. like that...

[innu]

Hi!

I have a problem with too many data in my table.

Its too much data in it, so I can't get all data with one query, so I have to make more queries.

I don't have an ID field - but I want to have sth. like a Startrow and an Endrow.

Does anybody know a solution for my problem? Any tips?

sl,
Innu

 

[rac2]

Is there some column in the table that is UNIQUE?
Are you using SQL Server 7 or later?
What language are you using for the application?

One approach would be to keep track of the last record retrieved using the value of the UNIQUE column in your application. Then use this value in the WHERE clause to select the next bunch of records. If you use a stored procdure you can pass it as a parameter; or you can build the query in your application using it.

Use the TOP keyword to limit the number of records retrieved. Another possibility is to use SET ROWCOUNT.

Here is the idea using ASP and JScript -

For the SQL query

SELECT TOP 50 uniqueColumn, colA, colB
FROM myBigTable 
WHERE uniqueColumn > @variableLastBunchHighValue
ORDER BY uniqueColumn

For the application part -

//Process the bunch of records retrieved by last query.


//Then save the high unique value.
//
rsBunch.moveLast();
variableLastBunchHighValue = rsBunch("uniqueColumn");

//Then build a new query using the high unique value.
//
strGetABunch = "SELECT TOP 50 uniqueColumn, colA, colB
FROM myBigTable 
WHERE uniqueColumn > " + variableLastBunchHighValue + 
" ORDER BY uniqueColumn" 

rsBunch.open(strGetABunch, objConnection);

I've never done this but it seems like it might work.