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

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 2

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