google bigquery - Execute an SQL Array having SQL Statements as Values

Question

I have a sql query which is able to return all the tables in a schema and use it to count number of id for a particular user id. I do so by using:-

DECLARE FIELDS_TO_CHECK ARRAY<STRING>;
DECLARE i INT64 DEFAULT 0;
CREATE TEMP TABLE `Queries` AS 
(SELECT 
   CONCAT('SELECT * FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME, 
          ' WHERE ', A.COLUMN_NAME, ' LIKE '%5-9c68-f79c8%';') as field1, A.TABLE_NAME as field2
FROM `db.INFORMATION_SCHEMA.COLUMNS` A where A.COLUMN_NAME = "user_id") ;
SET FIELDS_TO_CHECK = ['field1','field2'];
LOOP
 SET i = i + 1;
 IF i > ARRAY_LENGTH(FIELDS_TO_CHECK) THEN 
   LEAVE; 
 END IF;
 EXECUTE IMMEDIATE '''
   INSERT result
   SELECT FIELDS_TO_CHECK[ORDINAL(i)] 
   ''';
END LOOP; 
SELECT * FROM result;   

When I run the query it gives an error "Invalid value: Table name "result" missing dataset while no default dataset is set in the request. at [2:5]" How do i Solve this, or Basically the sql queries i have got via method 1 how can i run them and get a count of id and table name I am using Bigquery console to run this

question from:https://stackoverflow.com/questions/65889007/execute-an-sql-array-having-sql-statements-as-values

Answer 1

Waitting for answers