Teradata SQL - Very Large Pulls

Question

So I have a query that's returning more rows than MS Excel can handle (2 million), does anyone have any advice on getting Teradata to export to multiple Excel tabs, or maybe a way to break big query results up so that I can run the query to only pull more manageable chunks each time?

I know there are commands like TOP and SAMPLE which will only return a given portion of a pull, but I'm unclear on what the logic would be to rank a query and then return, say the TOP 100000, and then to run the same/similar query a second time but this time to have it return records 100001 - 200000, and then the time after that it returns 200001 - 300000, and so on.

Any tips would be greatly appreciated! I know I can export to a text CSV but it would definitely be easier if I can figure out a way for Teradata to play nice with Excel. Thanks!

Answer 1

You can use ROW_NUMBER() to assign an "ID" value to each row in your result set and then get the results in whatever chunks you want. Something like:

SELECT col1, col2, ...
FROM MyTable
QUALIFY ROW_NUMBER() OVER(
    ORDER BY Unique_Col
) BETWEEN <Start_Number> AND <End_Number>

Just make sure to use a Unique column or columns in the ORDER BY so that tbe ROW_NUMBER function applies the same ordering each time you run the query.

This doesn't take into account if there are changes to rows in your result set (i.e. added/deleted rows) in between query runs. If that's a concern, copy the rows first to a temp table and then run your queries against the temp table.