Execute Dynamic SQL Queries with EXECUTE IMMEDIATE in Bigquery - with Examples

Construct queries dynamically on the fly and execute them in Bigquery using EXECUTE IMMEDIATE. With this function, you can pass the column names, table names, insert values etc.. dynamically on the fly.

Example 1 : Insert into a table with a hard-coded Insert command

-- Add a row
EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES('Hamlet', 1599)";

Example 2 : Insert into a table using variables

-- create some variables
DECLARE book_name STRING DEFAULT 'Ulysses';
DECLARE book_year INT64 DEFAULT 1922;
DECLARE first_date INT64;
EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES(?, ?)"
  USING book_name, book_year;

Example 3 : Insert into a table using variables - alternate method

EXECUTE IMMEDIATE
  "INSERT INTO Books (title, publish_date) VALUES(@ name, @ year)"
  USING 1815 as year, "Emma" as name;

Example 4 : Dynamic Table name using variables

EXECUTE IMMEDIATE
  CONCAT("INSERT INTO ",@ book_name," (title, publish_date)", "VALUES('Middlemarch', 1871)");

Example 5 : Save the data from column to a variable

-- save the publish date of the first book, Hamlet, to a variable called first_date
EXECUTE IMMEDIATE "SELECT publish_date FROM Books LIMIT 1" INTO first_date;
+------------------+------------------+
| title            | publish_date     |
+------------------+------------------+
| Hamlet           | 1599             |
| Ulysses          | 1922             |
| Emma             | 1815             |
| Middlemarch      | 1871             |
+------------------+------------------+

nVector

posted on 05 Jun 20