Loop through and combine schemas/tables with UNION_ALL

Question

I want to combine tables from different schemas using UNION_ALL. The tables have the same schema, like in this toy example:

class1.names
+----------+
| id | name|
+----------+
| 1  | jon |
| 2  | ann |
| 3  | rob |

class2.names
+----------+
| id | name|
+----------+
| 1  | rav |
| 2  | meg |
| 3  | mat |

I can hardcode the list of classes into an array, or more preferably, obtain them using a query like so:

SELECT DISTINCT(TABLE_SCHEMA)
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE'

I want to combine the tables like so:

SELECT *, 'class1' as class FROM class1.names
UNION_ALL
SELECT *, 'class2' as class FROM class2.names
UNION_ALL
etc.

But there will be a lot more going on in the schema-level queries than SELECT *, 'class1'... so I want to do this using a loop or some other systematic method.

I'm looking at dynamic sql or using GROUP_CONCAT with 'UNION_ALL' as a separator, but I'm having trouble making progress.

Addendum: I know this is poor schema design but I can't do anything about that right now.

score 1 · Answer 1 · edited Nov 28 '19 at 14:32

In Snowflake, this dynamic SQL :

with a as (
  select * from information_schema.tables 
  where table_catalog like 'CLASS%' and table_name = 'NAMES' and table_type = 'BASE TABLE'
),

b as (
  select *, 
    $$SELECT *, 'SCHEMA' as class FROM SCHEMA.names$$ as t,
    replace(t,'SCHEMA',lower(table_schema)) as sql,
  from a
)

select listagg(sql,'\nUNION ALL\n') within group (order by table_schema, table_catalog)
from b;

will produce :

SELECT *, 'class1' as class FROM class1.names
UNION ALL
SELECT *, 'class2' as class FROM class2.names
UNION ALL
etc.

The $$...$$ are an alternative to single quotes for string literals. You can also escape single quotes by doubling them up.

score 0 · Answer 2 · answered Jul 08 '19 at 16:28

If i understand your correctly:

select listagg('select ' || f.value, ' union all ') from table(flatten(input => parse_json(
                                                    '[1, ,77]'))) f;
+----------------------------------------------+
| LISTAGG('SELECT ' || F.VALUE, ' UNION ALL ') |
|----------------------------------------------|
| select 1 union all select 77                 |
+----------------------------------------------+
1 Row(s) produced. Time Elapsed: 0.739s

and than:

select 1 union all select 77;
+----+
|  1 |
|----|
|  1 |
| 77 |
+----+
2 Row(s) produced. Time Elapsed: 0.638s

score 0 · Answer 3 · answered Nov 01 '19 at 17:03

Maybe something like:

SELECT DISTINCT case when row_number() OVER (ORDER BY lower(table_schema) || '.' || lower(table_name)) > 1 then 'UNION ALL ' else '' end ||
                   'SELECT *, ''' || lower(table_schema) || ''' AS class FROM ' || lower(table_schema) || '.' || lower(table_name) || '' union_stmt
FROM INFORMATION_SCHEMA.TABLES 
WHERE table_type = 'BASE TABLE'
and table_name like 'HPSA%';

Loop through and combine schemas/tables with UNION_ALL

3 Answers3

Linked