How can you pull every row from a table when you are accessing through an API that limits results to 500 records, and the table has no primary key?

Question

Our CRM has an API that allows us to run SQL queries but limits the results to 500 records. We need to get every row from certain tables that have more than 500 records.

My workaround so far for some tables has been to query the lowest primary key, the highest primary key, then run a query for 500 records at a time by primary key, and to keep repeating and adding each result set until we've reached the highest primary key (and then add all of those tables together). But some tables have no primary key. Is there a way to pull it 500 records at a time until the whole table has been pulled? This is SQL Server.

lowestPKQuery = "SELECT TOP 1 " + pkColumn + " FROM " + tableToDump + "ORDER BY " + pkColumn + " ASC";


highestPKQuery = "SELECT TOP 1 " + pkColumn + " FROM " + tableToDump + " ORDER BY " + pkColumn + " DESC";

initialRecordCountQuery = "SELECT count(" + pkColumn + ") FROM " + tableToDump;

// then, populate a dictionary that covers every range by intervals of 500

query = "SELECT " + columnsToSelect + " FROM " + tableToDump + " WHERE " + pkColumn + " BETWEEN " + range.Key + " AND " + range.Value + " ORDER BY " + pkColumn + " ASC";

// after all the tables have been pulled, then add all of those tables together

Is there a way just using SQL to eventually pull the entire table, 500 records at a time, when there is no primary key? It's basically a rest API that allows us to run almost any SQL statement (besides record deletion or table creation) but no matter what query, it will only return 500 records (in an XML document).

Having no primary key is bad design, but that doesn't mean that there isn't a combination of columns that is unique. I'd look for that. — TT., Jan 27 '19 at 08:53
Possible duplicate of [LIMIT 10..20 in SQL Server](https://stackoverflow.com/questions/971964/limit-10-20-in-sql-server) — Angelo Fuchs, Jan 27 '19 at 08:54
There is a problem with this approach that has to be taken into account: it doesn't work if the contents of the table change between reads. This can lead to inconsistent results -- anything from duplicate rows, missed rows and just outdated data. — Jeroen Mostert, Jan 27 '19 at 12:40

PSK · Answer 1 · 2019-01-27T08:59:58.903

You can use FETCH and OFFSET if you are using SQL Server 2012+ and ROW_NUMBER() to get the data from the older versions.

You can try like following query to get the data.

Tables with Primary Key

SELECT *   
FROM
    <Your_Table>
ORDER BY
    pkColumn  
OFFSET 0 ROWS 
FETCH NEXT 500 ROWS ONLY

Now keep increasing the OFFSET by 500 till you don't get any record.

Tables without Primary Key

For the table not having any primary key, you need to have a proper column to ORDER BY it may be a date column or a combination of multiple column, otherwise this approach will not work.

For example

SELECT *   
FROM
    <Your_Table>
ORDER BY
    CreatedDate
OFFSET 0 ROWS 
FETCH NEXT 500 ROWS ONLY

How can you pull every row from a table when you are accessing through an API that limits results to 500 records, and the table has no primary key?

1 Answers1