LIMIT in FoxPro

Question

I am attempting to pull ALOT of data from a fox pro database, work with it and insert it into a mysql db. It is too much to do all at once so want to do it in batches of say 10 000 records. What is the equivalent to LIMIT 5, 10 in Fox Pro SQL, would like a select statement like

select name, address from people limit 5, 10;

ie only get 10 results back, starting at the 5th. Have looked around online and they only make mention of top which is obviously not of much use.

Answer 1

I had to convert a Foxpro database to Mysql a few years ago. What I did to solve this was add an auto-incrementing id column to the Foxpro table and use that as the row reference.

So then you could do something like.

select name, address from people where id >= 5 and id <= 10;

The Foxpro sql documentation does not show anything similar to limit.

Answer 2

Take a look at the RecNo() function.

Answer 3

FoxPro does not have direct support for a LIMIT clause. It does have "TOP nn" but that only provides the "top-most records" within a given percentage, and even that has a limitation of 32k records returned (maximum).

You might be better off dumping the data as a CSV, or if that isn't practical (due to size issues), writing a small FoxPro script that auto-generates a series of BEGIN-INSERT(x10000)-COMMIT statements that dump to a series of text files. Of course, you would need a FoxPro development environment for this, so this may not apply to your situation...

Answer 4

To expand on Eyvind's answer I would create a program to uses the RecNo() funtion to pull records within a given range, say 10,000 records.

You could then programatically cycle through the large table in chucks of 10,000 records at a time and preform your data load into you MySQL database.

By using the RecNO() funtion you can be certain not to insert rows more than once, and be able to restart at a know point in the data load process. That by it's self can be very handy in the event you need to stop and restart the load process.