How to Write a FOR Loop in BigQuery

The loop variable is a struct holding one row of the query, and its fields are reached with dot notation. Every statement in the body is a separate query with its own overhead, so a loop over a thousand rows means a thousand queries.

FOR rec IN (SELECT table_name FROM analytics.INFORMATION_SCHEMA.TABLES)
DO
  SELECT rec.table_name;
END FOR;

Running dynamic SQL per row

The reason to loop at all is usually to build and run statements that cannot be expressed as one query, such as touching a list of tables. EXECUTE IMMEDIATE runs the assembled string.

FOR rec IN (
  SELECT table_name
  FROM analytics.INFORMATION_SCHEMA.TABLES
  WHERE table_name LIKE 'staging_%'
)
DO
  EXECUTE IMMEDIATE FORMAT('DROP TABLE analytics.%s', rec.table_name);
END FOR;

WHILE, BREAK and CONTINUE

WHILE covers the cases where the exit condition is not a row set. BREAK leaves the loop and CONTINUE skips to the next iteration; LEAVE and ITERATE are accepted as synonyms.

DECLARE i INT64 DEFAULT 0;
WHILE i < 10 DO
  SET i = i + 1;
  IF MOD(i, 2) = 0 THEN CONTINUE; END IF;
  SELECT i;
END WHILE;

Prefer set-based SQL

Most loops people write are a row-by-row version of something the engine can do in one pass. A loop that inserts one row at a time should be a single INSERT with a SELECT; a loop that updates rows conditionally should be a MERGE. Reach for scripting when the work is genuinely procedural, such as iterating over table names or draining a queue in batches.

Do more with BigQuery

• Export BigQuery to Google Sheets on a schedule
• Send BigQuery results to Slack
• BigQuery SQL client in the browser