How to create a 1M record table with a single query
1–10 of 40 posts
Re: How to create a 1M record table with a single query
#2> This is not a problem if your DBMS supports SQL recursion
A table of which DMBS’s support this would be useful
Re: How to create a 1M record table with a single query
#3If your DB doesn't support this technique, you might be able to use this rather disgusting technique, which builds an exponentially growing tower of rows by using nested queries with JOIN's. https://stackoverflow.com/a/61169467
Re: How to create a 1M record table with a single query
#4If you're running PostgreSQL, you can use the built-in generate_series (1) function like:
SELECT id, random()
FROM generate_series(0, 1000000) g (id);
[1] https://www.postgresql.org/docs/current/functions-srf.html
Re: How to create a 1M record table with a single query
#5> This is not a problem if your DBMS supports SQL recursion A table of which DMBS’s support this would be useful
I sort of assumed all of them did!
Re: How to create a 1M record table with a single query
#6Can someone give me a specific use case for this? As "check how a query behaves on a large table" is very vague to me.
E.g. I have a table structure but not alot of rows in it, so i go use this to get alot of rows in to check how fast queries get processed?
Re: How to create a 1M record table with a single query
#7> This is not a problem if your DBMS supports SQL recursion A table of which DMBS’s support this would be useful
This is the list that i could find:
Microsoft SQL Server, Firebird 2.1, PostgreSQL, SQLite, IBM Informix version, CUBRID, MariaDB and MySQL
Re: How to create a 1M record table with a single query
#8> This is not a problem if your DBMS supports SQL recursion A table of which DMBS’s support this would be useful
Not sure if it's up-to date though :
https://modern-sql.com/caniuse/with_recursive_(top_level)
Re: How to create a 1M record table with a single query
#9Can someone give me a specific use case for this? As "check how a query behaves on a large table" is very vague to me. E.g. I have a table structure but not alot of rows in it, so i go use this to get alot of rows in to check how fast queries get processed?
Exactly like that, yes.