Are you hard set on using common table expressions? I have discussed a bit off-list creating a data format that would allow tables to be read from a log-synth [1] schema. That would let you read as much data as you might like with an arbitrarily complex (or simple) query.
Operationally, you would create a file containing a log-synth schema that has the extension .synth. Your data source would have to be configured to connect that extension with the log-synth format. At that point, you could select as much or little data as you like from the file and you would see generated data rather than the schema. [1] https://github.com/tdunning/log-synth On Thu, Jul 9, 2015 at 11:31 AM, Alexander Zarei <[email protected] > wrote: > Hi All, > > I am trying to come up with a query which returns a given number of rows > without having a real table on Storage. > > I am hoping to achieve something like this: > > > http://stackoverflow.com/questions/6533524/sql-select-n-records-without-a-table > > DECLARE @start INT = 1;DECLARE @end INT = 1000000; > WITH numbers AS ( > SELECT @start AS number > UNION ALL > SELECT number + 1 > FROM numbers > WHERE number < @end)SELECT *FROM numbersOPTION (MAXRECURSION 0); > > I do not actually need to create different values and returning identical > rows would work too.I just need to bypass the "from clause" in the query. > > Thanks, > Alex >
