Loop in BigQuery (SQL - GOOGLE CLOUD)

Question

I would like to know how to perform a loop in bigquery to create a table changing only its name and the where clause. Basically as an example:

I would like, for example, to create the table three times according to vector_a, that is, we would have a table with the name 01,02,03 and filtering from vector_b that would also change to create the table with std1 at the beginning and then std2 and std3. Being these variables inside the array in string format.

Michael Entin · Answer

See https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in

You can use something like

DECLARE vector_a ARRAY;
SET vector_a = ['_01', '_02', '_03'];

FOR loop_variable_name IN (SELECT * FROM UNNEST(vector_a))
DO
  -- use loop_variable_name here;
END FOR;

Loop in BigQuery (SQL - GOOGLE CLOUD)

1 Answers