CODEWITHSUNDEEP
sqlgoogle-bigquery
user11035754
user11035754

Reputation: 227

Storing results of a stored procedure in a Google Big Query table

Here is a link to the question I asked earlier. The accepted answer works perfectly.

But this stored procedure processes 2 statements and after running this procedure I have to click on View Results of the second statement to see the result in Google Big Query. Is there a way to save the results in some table automatically using the 'Query Settings' in Google Big Query and specifying the name of the table I want to store the results in?

Upvotes: 1

Views: 2157

Answers (2)

p13rr0m
p13rr0m

Reputation: 1297

You can define a string parameter for your procedure. Then use this parameter in the dynamic query to write the results to the table.

CREATE OR REPLACE PROCEDURE `my_dataset.my_procedure`(destination_table STRING)
BEGIN

CREATE TEMP TABLE tmp AS SELECT 1 x;

EXECUTE IMMEDIATE (
    FORMAT("CREATE OR REPLACE TABLE `%s` AS SELECT * FROM tmp", destination_table));

END

Now you can provide a table name and call this procedure to write the results to the table.

CALL `my_dataset.my_procedure`("my_dataset.my_table");
SELECT * FROM `my_dataset.my_table`

Upvotes: 1

Yun Zhang
Yun Zhang

Reputation: 5503

You cannot set a destination table for a script (or for call a procedure), instead, you can convert your SELECT statement into CREATE TABLE ... AS SELECT, for example:

SELECT 1 x;

=>

CREATE TABLE myDataset.myTable
AS SELECT 1 x;

Upvotes: 3

Related Questions