Generate stuff with SQL 'connect by'

'connect by' was added in Oracle to build hierarchical queries, but is really usefull to generate STUFF.
(I think I first saw this usage from Johnathan Lewis... )

SQL> select rownum from dual connect by level <= 4;
         1
         2
         3
         4

Create a huge table:

With the help of RPAD:

SQL> select rpad('A',50,'B') from dual;
ABBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

We can create quickly a big table with data:

SQL> create table testbig as
             select rownum as n,  
                    rpad('A',50,'B') as txt 
             from dual connect by level <= 100000;


Somewhat more varied with modulo:

create table testbig2 as
  select rownum as n,  
         mod(rownum,100) as m, 
         rpad('A',50,'B') as txt 
  from dual connect by level <= 10000;



Generate days:
SQL> select TO_DATE('2012-01-01','YYYY-MM-DD')+rownum from dual connect  by level <=3;
2012-01-02
2012-01-03
2012-01-04




Generate months:
SQL> select ADD_MONTHS( TO_DATE('2012-01-01','YYYY-MM-DD'),rownum) from dual connect  by level <=3;
2012-02-01
2012-03-01
2012-04-01


Generate PARTITION clauses...



SQL> set pagesize 0
SQL> select 'PARTITION P'
||TO_CHAR(ADD_MONTHS( TO_DATE('2012-01-01','YYYY-MM-DD'),rownum),'YYYYMM')
||' VALUES LESS THAN (TO_DATE('''||TO_CHAR(ADD_MONTHS( TO_DATE('2011-01-01','YYYY-MM-DD'),rownum),'YYYYMMDD') ||''',''YYYYMMDD'')),'
from dual connect  by level <=3; 
PARTITION P201202 VALUES LESS THAN (TO_DATE('20110201','YYYYMMDD')),
PARTITION P201203 VALUES LESS THAN (TO_DATE('20110301','YYYYMMDD')),
PARTITION P201204 VALUES LESS THAN (TO_DATE('20110401','YYYYMMDD')),





Sadly I know no equivalent in MySQL,
(discussed here: stackoverflow.com/questions/701444 )


Of course in bash you can also generate things like so:

$ for i in {1..5}
> do
> echo PARTITION$i
> done
PARTITION1
PARTITION2
PARTITION3
PARTITION4

But I don't know an easy way to process dates though...