Create Temp Table in Each Loop and Union After Loop Completion

Question

Using BigQuery's standard SQL scripting functionality, I want to 1) create a temp table for each iteration of a loop, and 2) union those temp tables after the loop is complete. I've tried something like the following:

DECLARE i INT64 DEFAULT 1;
DECLARE ttable_name STRING;

WHILE i < 10 DO
    SET ttable_name = CONCAT('temp_table_', CAST(i AS STRING));
    CREATE OR REPLACE TEMP TABLE ttable_name AS
        SELECT * FROM my_table AS mt WHERE mt.my_col = 1;
    SET i = i + 1;
END LOOP;

SELECT * FROM temp_table_*; -- wildcard table to union all results

But I get the following error:

Exceeded rate limits: too many table update operations for this table.

How can I accomplish this task?

Answer 1

Don't create new tables. Add to an existing one with an INSERT INTO, or hold data in a variable (if it's not too much data), as in:

DECLARE steps INT64 DEFAULT 1;
DECLARE table_holder ARRAY<STRUCT<steps INT64, x INT64, y ARRAY<INT64>>>;

LOOP 
  SET table_holder = (
    SELECT ARRAY_AGG(
      STRUCT(steps, 1 AS x, [1,2,3] AS y))
    FROM (SELECT '')
  );
  SET steps = steps+1;
  IF steps=30 THEN LEAVE; END IF;
END LOOP;

CREATE TABLE temp.results
AS
SELECT *
FROM UNNEST(table_holder)

Related: https://stackoverflow.com/a/59314390/132438

Answer 2

Your script does not work the way you think it does!

Instead of writing in each iteration into separate table named like temp_table_N - you actually writing to the very same temp table named ttable_name - thus the Exceeded rate limits error

BigQuery does not allow using variables for objects names

Answer 3

Question asker/OP here. While I have selected @felipe-hoffa's answer as I believe it will be best for future readers of this question, I have actually gone a different route in solving my problem:

BEGIN
    DECLARE i INT64 DEFAULT 1;


    CREATE OR REPLACE TEMP TABLE ttable AS
        SELECT
            CAST(NULL AS INT64) AS col1 -- cast NULL as the type of target col
            ,CAST(NULL AS FLOAT64) AS col2
            ,CAST(NULL AS DATE) AS col3;

    WHILE i < 10 DO

        -- overwrite `ttable` with its previous contents union'ed
        -- with new data results from current loop iteration
        CREATE OR REPLACE TEMP TABLE ttable AS
            SELECT mt.col1, mt.col2, mt.col3 FROM my_table AS mt WHERE mt.other_col = i
            UNION ALL
            SELECT * FROM ttable;

        SET i = i + 1;
    END LOOP;

    SELECT * FROM ttable; -- UNION'ed results

    DROP TABLE IF EXISTS ttable;

END;

Why? I find it easier to stay in "table land" than to venture into "STRUCT/ARRAY land".