3 ms·
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: sel
by slt2021 6y ago
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
- vp8989 6y agoThis 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.
- kroolik 6y agoAlso 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!