Just install any Oracle middleware or Oracle product, The Oracle H1B's will have your tablespace all used up in a matter of days with their shit code.
How to create a 1M record table with a single query
31–40 of 40 posts
Re: How to create a 1M record table with a single query
#32usually 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
Re: How to create a 1M record table with a single query
#33Earlier quoted context omitted.
If you're running PostgreSQL you can also just xxd -ps -c 16 -l $bignum Depending on how you want your random keys formatted.
If you're importing from command line tools, you might as well use the specialized `shuf` tool: shuf -i 1-$bignum or for random numbers with replacement shuf -i 1-$bignum -r -n $bignum These give you a sample of random integers. `shuf` is part of GNU coreutils, so present in most Linux installs (but not on macOS).
Nice yeah that's a good way. Generally the number 1 million is so small I see no reason to do this in any manner other than shell commands.
Re: How to create a 1M record table with a single query
#34Earlier quoted context omitted.
If you're running PostgreSQL you can also just xxd -ps -c 16 -l $bignum Depending on how you want your random keys formatted.
If you're importing from command line tools, you might as well use the specialized `shuf` tool: shuf -i 1-$bignum or for random numbers with replacement shuf -i 1-$bignum -r -n $bignum These give you a sample of random integers. `shuf` is part of GNU coreutils, so present in most Linux installs (but not on macOS).
It's worth installing GNU coreutils on macOs All the command names are prepended with g, so `gshuf`
Re: How to create a 1M record table with a single query
#35usually 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.
Re: How to create a 1M record table with a single query
#36usually 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!
Re: How to create a 1M record table with a single query
#37i generally end up writing a data generator using the language and apis in the application. I often want control over various aspects of the data and built the generator as such. Quite often I just generate queries and then run them which works quickly.
While this looks like a good way to generate simple data, practical applications are more involved.
Re: How to create a 1M record table with a single query
#38Earlier quoted context omitted.
Here's how to do something similar in Q (KDB) ([]x?x:1000000) Which gives a table of a million rows like: x ------ 877095 265141 935540 49015 ...
Nothing to do with the article, SQL, or a DB, but I can't help wanting to add even just a bit when I see array languaes mentioned. So, in J it's just: ?~1000000 or, if you don't need elements to be unique: ?$~1000000 Though it's just an array, not a persistent table - I don't know much about Jd :(
An R vector would be `1:1000000`
Re: How to create a 1M record table with a single query
#39Earlier quoted context omitted.
Nothing to do with the article, SQL, or a DB, but I can't help wanting to add even just a bit when I see array languaes mentioned. So, in J it's just: ?~1000000 or, if you don't need elements to be unique: ?$~1000000 Though it's just an array, not a persistent table - I don't know much about Jd :(
An R vector would be `1:1000000`
Nope, that's - as far as I can see - just a sequence of increasing integers. Both K and J examples give an array of random integers in 0-1000000 range.
For reference, in J such sequence of integers can be generated with:
1+i.1000000
1 2 3 4 5 6 7 8 9 10 ....
1+i.1000000 1
1
2
3
4
5
6
7
8
9
10
....
5 2 $ 10
10 10
10 10
10 10
10 10
10 10
5 2 $ 10 11 12
10 11
12 10
11 12
10 11
12 10
V~ x NB. same as x V x
$~ 10
10 10 10 10 10 10 10 10 10 10
? $~ 10
1 6 9 4 6 8 8 7 9 4
?~10
6 9 7 3 5 1 8 0 4 2
2*i.10
0 2 4 6 8 10 12 14 16 18
(?~10){2*i.10
14 10 18 6 2 12 8 0 16 4
All the primitives (words) are described here: https://code.jsoftware.com/wiki/NuVoc
Re: How to create a 1M record table with a single query
#40If 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); There seems to be an equivalent in SQLite: https://sqlite.org/series.html [1] https://www.postgresql.org/docs/current/functions-srf.html
you have to use it since PostgreSQL does not support limit within a with expression.
You can use a with-expression. It's true that you can't use `limit` to limit it, but you can use a `where` condition. This is equivalent to cribwi's example:
WITH RECURSIVE
t AS (
SELECT 0 id
UNION ALL
SELECT id + 1
FROM t
WHERE id