CODEWITHSUNDEEP
google-bigquery
Nicolas Rondon
Nicolas Rondon

Reputation: 125

How to query all tables in dataset and add an identifier?

I use this query

 SELECT * FROM `project.DATASET.*`

to select all the data in DATASET, There is a way to add a new column to identify which table belong each record?

Upvotes: 0

Views: 462

Answers (1)

shollyman
shollyman

Reputation: 4384

You're using a wildcard query, which support a special _TABLE_SUFFIX identifier. Most use it for filtering the set of matched tables, but you can project it as a result column as well.

More info here: https://cloud.google.com/bigquery/docs/querying-wildcard-tables

Something like this:

SELECT
  _TABLE_SUFFIX as src_tbl,
  * 
FROM `project.dataset.*`

Upvotes: 3

Related Questions