I have a procedure saved in a dataset (project.dataset_x.sp_test) and this procedure have statments that create tables in the same dataset of the procedure (project.dataset_x.d_test).
The goal is to change all the datasets dataset_x to dataset_y dynamically.
Example:
CHANGE
Create or replace procedure `project.dataset_x.sp_test`()
BEGIN
Create or replace table `project.dataset_x.d_test` AS
Select
1 a id,
'aaaa' as name
FROM `project.dataset_x.d_init`
END
TO
Create or replace procedure `project.dataset_y.sp_test`()
BEGIN
Create or replace table `project.dataset_y.d_test` AS
Select
1 a id,
'aaaa' as name
FROM `project.dataset_y.d_init`
END
Which is the best approach to create a scrip on Bigquery to change the datasets dynamically?
Try the following:
Using the above you do not have to create multiple procedures but can still achieve the desire results