How to UNION ALL tables with tablename satisfying condition in BigQuery?-CodePudding

I can unify two tables via:

select * from project.dataset.ourtable1
union all
select * from project.dataset.ourtable2

But what if I have thousands of tables, and I want to unify all which have name starting with ourtable?

I can get all such tables via:

select table_id from project.dataset.__TABLES__
where starts_with(table_id,'ourtable')

which returns a column of tables with table_id starting with ourtable.

How do I perform union all on all of them?

To rephrase the question: I am looking for the equivalent of

select * from project.dataset.ourtable1
union all
select * from project.dataset.ourtable2
union all
.
.
.
union all
select * from project.dataset.ourtable9999

in BigQuery.

A similar thread: here, but it is for SQL-Server, not BQ.

CodePudding user response：

You can use a wildcard:

select * from `project.dataset.ourtable*`