usually there is a system table with a ton of metadata that will for sure contain few thousand rows, so generating a million rows for SQL Server is simply:
select top 1000000 ROW_NUMBER()
from sys.objects a, sys.objects b
or you can use INFORMATION_SCHEMA which is more portable across different RDBMS engines
Also one of the only ways to get sequences in joins in Redshift. Unfortunately, only Redshift master nodes support 'generate_series'. If your query contains join that are spread across multiple worker nodes, Redshift will report an error saying 'generate_series' no supported.
Comments
usually there is a system table with a ton of metadata that will for sure contain few thousand rows, so generating a million rows for SQL Server is simply:
select top 1000000 ROW_NUMBER() from sys.objects a, sys.objects b
or you can use INFORMATION_SCHEMA which is more portable across different RDBMS engines
This is how I do it. You can cross join the derived table to itself if you need more rows.
Much more performant and naturally relational way of generating data than looping recursively.
Also one of the only ways to get sequences in joins in Redshift. Unfortunately, only Redshift master nodes support 'generate_series'. If your query contains join that are spread across multiple worker nodes, Redshift will report an error saying 'generate_series' no supported.
Gotta select row number on some big enough table!