DYNAMIC QUERIES in GCP Big Query

Question

I'm trying to execute this dynamic query in GCP bigquery.

EXECUTE IMMEDIATE 'INSERT INTO `project.dataset.tbl` (MARKET, PARAMETERS, KPI, VALUE_TYPE, `03_2023`, `04_2023`, `05_2023`, `06_2023`, `07_2023`, `08_2023`, `09_2023`, `10_2023`, `11_2023`, `12_2023`, `01_2024`, `02_2024`, `03_2024`, `04_2024`, `05_2024`, `06_2024`, `07_2024`, `08_2024`, `09_2024`, `10_2024`, `11_2024`, `12_2024`, `01_2025`, `02_2025`, `03_2025`, `04_2025`, HIST_DATE) SELECT A.MARKET, A.PARAMETERS, A.KPI, A.VALUE_TYPE, @A , @B ,cast(FORMAT_DATE(\'%Y-%m-%d\',@LD)as date) FROM ( SELECT * FROM `project.dataset.tbl` WHERE LAST_DAY(HIST_DATE) = LAST_DAY(DATE_ADD(cast(FORMAT_DATE(\'%Y-%m-%d\',@LD)as date),INTERVAL -1 MONTH)) ) AS A INNER JOIN `project.dataset.tbl2` AS B ON A.MARKET = B.MARKET AND A.PARAMETERS = B.PARAMETERS AND A.KPI = B.KPI AND A.VALUE_TYPE = B.VALUE_TYPE' USING COLUMN_A AS A,COLUMN_B AS B, LAST_DATE AS LD;

The value of column_A is A.03_2023, A.04_2023, A.05_2023, A.06_2023, A.07_2023, A.08_2023, A.09_2023, A.10_2023, A.11_2023 which is replacing @A in the dynamic query. While the value of column_B is B.12_2023, B.01_2024, B.02_2024, B.03_2024, B.04_2024, B.05_2024, B.06_2024, B.07_2024, B.08_2024, B.09_2024, B.10_2024, B.11_2024, B.12_2024, B.01_2025, B.02_2025, B.03_2025, B.04_2025 which is replacing @B in the dynamic query.

However, the insert fails saying that the select returns only 7 rows while insert has 31 expected. What do I do so it takes the @A is replaced with the value of COLUMN_A and @B is replaced with column_B and the total is 31 columns?

Answer 1

I was able to put the string value present in my COLUMN_A variable. The FROM clause did the trick. I did not include @A as a place-holder in the solution. Simply put a concatenation break using '||A_COLUMN_NAME||'. A_COLUMN_NAME field of table STRING_AGGREGATED_COLUMNS has the string I was trying to substitute previously using @A.

set COLUMN_NAME_QUERY =(select  'INSERT INTO `project.dataset.tbl`  (MARKET, PARAMETERS, KPI, VALUE_TYPE,  `03_2023`,    `04_2023`,  `05_2023`,  `06_2023`,  `07_2023`,  `08_2023`,  `09_2023`,  `10_2023`,  `11_2023`,  `12_2023`,  `01_2024`,  `02_2024`,  `03_2024`,  `04_2024`,  `05_2024`,  `06_2024`,  `07_2024`,  `08_2024`,  `09_2024`,  `10_2024`,  `11_2024`,  `12_2024`,  `01_2025`,  `02_2025`,  `03_2025`,  `04_2025`, HIST_DATE) SELECT A.MARKET, A.PARAMETERS, A.KPI, A.VALUE_TYPE, '||A_COLUMN_NAME||','||B_COLUMN_NAME||' ,cast(FORMAT_DATE(\'%Y-%m-%d\','||LAST_DATE||')as date) FROM ( SELECT * FROM `project.dataset.tbl`       WHERE LAST_DAY(HIST_DATE) = LAST_DAY(DATE_ADD(cast(FORMAT_DATE(\'%Y-%m-%d\','|| LAST_DATE || ')as date),INTERVAL -1 MONTH))) AS A INNER JOIN `project.dataset.tbl2`  AS B ON A.MARKET = B.MARKET AND A.PARAMETERS = B.PARAMETERS        AND A.KPI = B.KPI AND A.VALUE_TYPE = B.VALUE_TYPE' 
from STRING_AGGREGATED_COLUMNS);

EXECUTE IMMEDIATE (COLUMN_NAME_QUERY);