CODEWITHSUNDEEP
google-bigquery
ysd
ysd

Reputation: 301

add table_id to the result from multiple tables in BigQuery

Below is how I structured the data in BigQuery database.

test
  -> sales
    -> monthly-2015
    -> monthly-2016
    -> ...

I want to combine the data of all tables with the table name , monthly-*, and below is how I wrote the sql from examples I found.
Running this sql leads an error like following Scalar subquery produced more than one element. How could I fix it to error?

SELECT
  *,
  (
  SELECT
    table_id
  FROM
    `test.sales.__TABLES_SUMMARY__`
  WHERE
    table_id LIKE 'monthly-%')
FROM
  `test.sales.monthly*`

Upvotes: 1

Views: 37

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

I want to combine the data of all tables with the table name , monthly-*

Try below

SELECT *, 'monthly_' || _TABLE_SUFFIX as table_name 
FROM `test.sales.monthly_*`

Upvotes: 1

Related Questions