Big query temporary table

[liorro]

hi,

how can i use temporary tables (BQ) in sql statement:

begin

   CREATE TEMP TABLE tmp_table as

   select ID,Country

   FROM `prod.Main.Entities` AS entities

   where entities.RegistrationDate >='2023-01-01';

End;  

begin

select temp.D , Countries.CountryName

from tmp_table as temp

LEFT JOIN `prod.Main.Countries` AS Countries ON temp.Country = Countries.ISO2;

END;

[amitchandak · Accepted solution]

@liorro , Try with clause

with temp as(

CREATE TEMP TABLE tmp_table as

select ID,Country

FROM `prod.Main.Entities` AS entities

where entities.RegistrationDate >='2023-01-01';

)

select temp.D , Countries.CountryName

from tmp_table as temp

LEFT JOIN `prod.Main.Countries` AS Countries ON temp.Country = Countries.ISO2;

[liorro]

thanks!

if i need more then 1 temp table? or if i have performance issue?

is there any other option?