Skip to Content

Create a Number Table with 1 to N Rows

Step by Step Guide

This article will guide you through how to generate numbers from 1 to N using only Vinyl data objects, without having to store them in a Number table.

  1. Create a Subquery that will SELECT 1 as N. We will call this first subquery Row Numbers 1.
  2. Create another Subquery called Row Numbers 2 which will Union the Row Numbers 1 to itself. This will give two records.
  3. Create another Subquery called Row Numbers 4 that Crosses Row Numbers 2 to itself to give four records.

  4. You can keep crossing the previously made Subquery to get exponentially more:

    • Row Numbers 1
    • Row Numbers 2
    • Row Numbers 4
    • Row Numbers 16
    • Row Numbers 256
    • Row Numbers 65,536
    • Row Numbers 4,294,967,296

    Note

    If you do not need 4 billion records, you can just Cross any combination of these Subqueries together to get the amount you need. For example: Cross join Row Numbers 65,536 with Row Numbers 16 to get Row Numbers 1,048,576.

  5. Finally, create one more Subquery that uses ROW_NUMBER() to display your data, called Row Number.