How to union multiple times same SQL

Question

I have query like this

select 1,2,3 from dual
union all
select 1,2,3 from dual

When I need to add new row, i put another union all, and that's ok. But problem appear when I need several union, for example 20. It is really annoying and not efficient to make another 17 unions. Is there a way (some procedure, function whatever) to make it faster and more elegant?

SQL is not very good at generating data, but rather querying it and serving out results on data which already exists. One approach here would be to just generate a flat CSV file with however many rows you want, then use Oracle's loader tool to move it into a table. — Tim Biegeleisen, Aug 27 '20 at 10:45
There is a similar mysql question posed here: https://stackoverflow.com/questions/33402899/better-way-to-repeat-queries-instead-of-union-all - not sure if Oracle will allow something similar.. — JGFMK, Aug 27 '20 at 11:08

score 4 · Accepted Answer · answered Aug 27 '20 at 10:52

No problem, easy-peasy.

SQL> select 1, 2, 3
  2  from dual
  3  connect by level <= 10;

         1          2          3
---------- ---------- ----------
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3

10 rows selected.

SQL>

score 0 · Answer 2 · answered Aug 27 '20 at 11:17

0

Sometimes it's easier to use json_table in such cases:

select *
from json_table(
  '{data:[
     [1,2,3,"abc"],
     [2,3,4,"def"],
     [3,4,5,"xyz"],
    ]
   }'
   ,'$.data[*]'
   columns
      a number path '$[0]',
      b number path '$[1]',
      c number path '$[2]',
      d varchar2(30) path '$[3]'
);

Results:

         A          B          C D
---------- ---------- ---------- ------------------------------
         1          2          3 abc
         2          3          4 def
         3          4          5 xyz

answered Aug 27 '20 at 11:17

Sayan Malakshinov

8,492
1
19
27

1

It's pretty embarrassing I think, that Oracle still doesn't support the standard `values` [row constructor](https://dbfiddle.uk/?rdbms=postgres_12&fiddle=16bb76003083dae0c350c15f0c99321a) – Aug 27 '20 at 11:23
@a_horse_with_no_name yeah, I often miss it... and generate_series... but considering how CBO is complex already and how many features/transformations/etc it supports, I think that's not so bad to be a bit conservative. At least, I think it's better to have good planner than huge amount of syntax constructions with much more bugs. – Sayan Malakshinov Aug 27 '20 at 11:32

score 0 · Answer 3 · answered Aug 27 '20 at 15:33

0

A variation on Littlefoot's answer:

select 1, 2, 3
from   xmltable('1 to 20');

answered Aug 27 '20 at 15:33

William Robertson

15,273
4
38
44

How to union multiple times same SQL

3 Answers3