May 13, 2002 at 2:13 am
I am trying to write a utility which will go to each table in a database and query each table in the database. The size of the table won't be known at the time of writing the code and neither any other specifics about the structure of the table. Hence I want to make my code such that it gets a max of around 1 lakh rows to the front end. Once these rows are processed next set of rows are brought in. This way the entire table is scanned and processed. I know I can limit the number of rows by setting row count to 100000 or x value but don't have any clue as to how to get to know which was the last record that I brought in last and as to how to get next set of relevant records. Is there any way by which I can keep a pointer to record and get records subsequent to it. I don't have any control on the structure of the tables and basically want to store the contents to XML but my utility is expected to work with SQL 6.5, SQL 7.0 and SQL 2000 and may be INFORMIX (Later on) so cannot just work with integrated features of SQL - XML in SQL 2000.
Any help would be greatly appreciated.
Thanks
May 13, 2002 at 5:38 am
The easiest way I know of without holding a cursor open during the process (ADO implemented cursor) is to use an IDENTITY column or a unique value column that you can sort on then when you use the order by clause on you recordset and when you return based on the value of the last record you can do a greater than the last value you had. Will you have these or is this a problem as well. If so then all I can see is and ADO cursor.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 13, 2002 at 8:40 am
Having a Identity Column does not help me as I don't have any control on the table structure. Is there any other way that we can use to solve this problem.
May 13, 2002 at 4:48 pm
From the standpoint of an application I believe your best bet is to use a forward only cursor with ADO in your application. This way you can go thru the records one at a time and you can move to the next record until done, then you can drop your recordset and build a new one for the next table. Cannot see a pure SQL solution without structures on the other end being known. If you have questions let me know.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 14, 2002 at 9:18 am
You can do it this way.
Select Top 100
PolicyID
From PolicyTable
Order BY PolicyID
When you hit last record in the RS USe The id in where clause for the same SQL and retrieve the following 100 records.
Select Top 100 PolicyID
From PolicyTable
Where PolicyID > (Your last Record ID in RS)
Order BY PolicyID
Thou you might have concurrency issues with this approach
May 14, 2002 at 9:31 pm
Thanks Antares686 for your inputs Yep I guess I have only ADO as my best bet. I have used that and it seems to be working well. However I used Set RowCount options so that queries do start sending back results faster and sets which can be handled otherwise for large databases both client and server seems to be hanging. Thanks for all your inputs. I guess for the time being my problem has been resolved.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply