Big Query Scripting: automatically save BQ output of one procedure into a Table

Question

following up on this question regarding BigQuery scripting: [https://stackoverflow.com/questions/65877988/bigquery-scripting-running-a-sql-query-based-on-each-row-included-in-a-table-co][1]

when I execute a BQ script:

execute immediate (
  SELECT  'select page_slug, simpleSku from (' || string_agg(
    format('select "%s" as page_slug, array(%s)as simpleSkus', page_slug, page_sql_new), 
    ' union all '
  ) || '), unnest(simpleSkus) as simpleSku'
  FROM `xxx.master`
);   

2 results are visible in the interface

is there a way to save the result of the 2nd procedure as a table programmatically?

Answer 1

Try below

execute immediate (
  SELECT  'insert `project.dataset.table` select page_slug, simpleSku from (' || string_agg(
    format('select "%s" as page_slug, array(%s)as simpleSkus', page_slug, page_sql_new), 
    ' union all '
  ) || '), unnest(simpleSkus) as simpleSku'
  FROM `xxx.master`
);      

or (if table does not exists yet)

execute immediate (
  SELECT  'create table `project.dataset.table` as select page_slug, simpleSku from (' || string_agg(
    format('select "%s" as page_slug, array(%s)as simpleSkus', page_slug, page_sql_new), 
    ' union all '
  ) || '), unnest(simpleSkus) as simpleSku'
  FROM `xxx.master`
);      

or (if you want to replace table in case if it exists)

execute immediate (
  SELECT  'create or replace table `project.dataset.table` as select page_slug, simpleSku from (' || string_agg(
    format('select "%s" as page_slug, array(%s)as simpleSkus', page_slug, page_sql_new), 
    ' union all '
  ) || '), unnest(simpleSkus) as simpleSku'
  FROM `xxx.master`
);