Reputation: 9
Querying multiple tables using UNION ALL, and the various tables do not have a field that differentiates the data from each table and so the idea is to populate a field with the table name or other identifier in order to differentiate the data based on what table it originated from.
Select _TABLE_SUFFIX
SELECT
Posting_Date,
Entry_No_,
Document_No_,
G_L_Account_No_,
Global_Dimension_1_Code,
Global_Dimension_2_Code,
Description,
Amount
FROM
`1_G_L_Entry`
WHERE
Posting_Date > '2019-03-31'
AND G_L_Account_No_ >'399999'
UNION ALL
SELECT
Posting_Date,
Entry_No_,
Document_No_,
G_L_Account_No_,
Global_Dimension_1_Code,
Global_Dimension_2_Code,
Description,
Amount
FROM
`2_G_L_Entry`
WHERE
Posting_Date > '2019-03-31'
AND G_L_Account_No_ >'399999'
UNION ALL
SELECT
Posting_Date,
Entry_No_,
Document_No_,
G_L_Account_No_,
Global_Dimension_1_Code,
Global_Dimension_2_Code,
Description,
Amount
FROM
`3_G_L_Entry`
WHERE
Posting_Date > '2019-03-31'
AND G_L_Account_No_ >'399999'
UNION ALL
SELECT
Posting_Date,
Entry_No_,
Document_No_,
G_L_Account_No_,
Global_Dimension_1_Code,
Global_Dimension_2_Code,
Description,
Amount
FROM
`4_G_L_Entry`
WHERE
Posting_Date > '2019-03-31'
AND G_L_Account_No_ >'399999'
UNION ALL
SELECT
Posting_Date,
Entry_No_,
Document_No_,
G_L_Account_No_,
Global_Dimension_1_Code,
Global_Dimension_2_Code,
Description,
Amount
FROM
`5_G_L_Entry`
WHERE
Posting_Date > '2019-03-31'
AND G_L_Account_No_ >'399999'
UNION ALL
SELECT
Posting_Date,
Entry_No_,
Document_No_,
G_L_Account_No_,
Global_Dimension_1_Code,
Global_Dimension_2_Code,
Description,
Amount
FROM
`7_G_L_Entry`
WHERE
Posting_Date > '2019-03-31'
AND G_L_Account_No_ >'399999'
UNION ALL
SELECT
Posting_Date,
Entry_No_,
Document_No_,
G_L_Account_No_,
Global_Dimension_1_Code,
Global_Dimension_2_Code,
Description,
Amount
FROM
`8_G_L_Entry`
WHERE
Posting_Date > '2019-03-31'
AND G_L_Account_No_ >'399999'
ORDER BY
Entry_No_ ASC
Selects the table name from each table queried or other method to differentiate the data...
Upvotes: 0
Views: 2000
Reputation: 59375
_table_suffix
will only work when you have a *
expansion:
SELECT _table_suffix suffix, COUNT(*) c
FROM `fh-bigquery.stackoverflow_archive_questions.q*`
GROUP BY 1
ORDER BY 1 DESC
But when doing an UNION
you'll need to manually 'tag' each table:
SELECT table, COUNT(*) c
FROM (
SELECT *, '201703' table
FROM `fh-bigquery.stackoverflow_archive_questions.q201703`
UNION ALL
SELECT *, '201706' table
FROM `fh-bigquery.stackoverflow_archive_questions.q201706`
UNION ALL
SELECT *, '201709' table
FROM `fh-bigquery.stackoverflow_archive_questions.q201709`
)
GROUP BY 1
ORDER BY 1 DESC
Upvotes: 3